Sql select min return value from another column in the same row with grouping

Question

Sql select min return value from another column in the same row with grouping

I need an OrderType for min (Date) for each name. So, I want this:

Name    Date        OrderType
Alex    1/1/2014    Direct
Alex    9/15/2014   Distributor
Cindy   6/4/2014    Distributor
John    5/8/2014    Direct
John    2/14/2014   Distributor

to get this back:

Name    Date        OrderType
Alex    1/1/2014    Direct
Cindy   6/4/2014    Distributor
John    2/14/2014   Distributor

+3

sql sql-server tsql

Scott leas Sep 18 14 at 20:50

source to share

4 answers

I think you need to select the min date per person and then join the original table to get the type for that row.

Assuming your table is called a tab and each person only had one order per date (otherwise the question is not possible) then something like:

Select t.name, t.date, t.ordertype
From tab t,
     ( select min (i.date) date, i.name from tab i group by i.name) t2
Where t.date = t2.date
  And t.name = t2.name

Sorry that I am working mainly with mysql and Oracle, not tsql, so this is the general sql syntax.

+1

Adam Sep 18 14 at 21:01

source to share

CREATE TABLE #tmp
( Name VARCHAR(50), orderdate DATE, OrderType VARCHAR(50) )
INSERT #tmp ( Name, orderdate, OrderType )
VALUES  ( 'Alex', '01/01/2014','Direct')
INSERT #tmp
VALUES  ( 'Alex', '9/15/2014','Distributor')
INSERT #tmp
VALUES  ( 'Cindy', '6/4/2014','Distributor')
INSERT #tmp
VALUES  ( 'John', '5/8/2014','Direct')
INSERT #tmp
VALUES  ( 'John', '2/14/2014','Distributor')


; WITH CTE_RESULT AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ORDERDATE ASC) ROW_NO , Name, orderdate, OrderType
    FROM #tmp
)

SELECT  * FROM CTE_RESULT T WHERE T.ROW_NO=1

0

singhswat Sep 18 14 at 21:18

source to share

try it

select 
a.name, 
MIN(a.date)as date, 


from dbname a i
inner join (select name, min(date), ordertype from dbname) b on b.name=a.name and a.date=b.date

group by a.name, b.ordertype

-2

lookslikeanevo Sep 18 '14 at 21:00

source to share

radar · Accepted Answer · 2014-09-18T20:56:24+0000

We can get the row number based on the date for each [Name]

and select the entry with the lowest date.

SELECT [T].* 
FROM (  
    SELECT [Name]
         , [DATE]
         , [OrderType]
         , ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date]) AS [seq]
    FROM [TableA]
) AS [T]
WHERE [T].[seq] = 1

Sql select min return value from another column in the same row with grouping

More articles: