asked on

Need help selecting top record by date

I would like to make a select statement, where it will return the first record by the highest ID and also a count of how many total there are of that same "order number" Is this possible with SQL?

For example, for the dataset below, I would like the record to return only 1 instance of the "order number" and a count of how many different ones (same order number, other records) there are.

Can someone help with this? Thank you.

I would like the return to show this, from the dataset below it.

Query return:
ID | Order Number | Count | Date | SomethingElse
3 1234567 3 7/28/15 hello again
5 7896548 2 7/30/15 cool

Data set:
ID | Order Number | Date | SomethingElse
1 1234567 7/25/15 blah
2 1234567 7/26/15 hello
3 1234567 7/28/15 hello again
4 7896548 7/28/15 test
5 7896548 7/30/15 cool

Mike Eghtebas

Select Max(tt.ID) As ID
   , tt.[Order Number]
   , Count(tt.ID) As Count
   , Max(tt.[Date] As OrderDate
   ,(Select t.SomethingElse From Table1 t Where t.ID =tt.ID) As  Something_Else
From Table1 tt
Group By tt.[Order Number]

Open in new window

YZlat

SELECT ID, Table1.OrderNumber, Date, a.OrderCount, SomethingElse
FROM Table1
INNER JOIN (SELECT MAX(ID) As MAxID, COUNT(OrderNumber) As OrderCount, OrderNumber FROM Table1 GROUP BY OrderNumber) as a ON ID=a.MAxID and Table1.OrderNumber=a.OrderNumber

Open in new window

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

I had an error, try this it is tested:

Select  D.ID dd, D.[Order Number]
      , (Select Count([Order Number]) From #Table1 t 
         Where t.[Order Number]=D.[Order Number]) As [Count]
      , OrderDate
      , D.SomethingElse
From(Select  
     row_number() Over(Partition By [Order Number] Order By ID Desc) As rn
     , ID
     , [Order Number]
     , [Date] As OrderDate
     , SomethingElse
From #Table1) As D 
Where rn = 1

Open in new window

Temptable used:

create table #Table1(ID int, [Order Number] int, [Date] date, SomethingElse varchar(25));
insert into #Table1(ID, [Order Number], [Date], SomethingElse) Values
( 1,     1234567,                  '7/25/15',     'blah') 
 ,(2 ,    1234567   ,               '7/26/15',     'hello')
 ,(3 ,    1234567  ,                '7/28/15',     'hello again')
,( 4 ,    7896548 ,                 '7/28/15',     'test')
,( 5 ,    7896548 ,                 '7/30/15',     'cool');

Open in new window

Brendt Hess

This is a straightforward method of returning the data needed.

SELECT 
   ID, 
   [Order Number], 
   Count, 
   Date, 
   SomethingElse  
FROM myTable t
INNER JOIN (
   SELECT max(ID} as maxID,
      Count(*) as Count
   FROM myTable
   GROUP BY [Order Number]
   ) f
   ON t.id = f.maxID
ORDER BY t.id

Open in new window

Anoo S Pillai

A code similar to the following will server the purpose. This is very similar to the code posted by eghtebas in previous post. The only difference is on how count is taken. There is no need to scan the table again to get the count. Better to have indexes on ID and OrderNumber columns for performance. More about ROW_NUMBER

SELECT TempTab.ID
      , TempTab.OrderNumber 
      , TempTab.OrderCount 
      , TempTab.[Date]
      , TempTab. SomethingElse
FROM  (  
        SELECT ID
	      , OrderNumber 
	      , COUNT(1) OVER ( PARTITION BY OrderNumber )  OrderCount  
	      , [Date] 
	      , SomethingElse
              , ROW_NUMBER() OVER ( PARTITION BY OrderNumber ORDER BY ID DESC ) as RowNum
        FROM <<YourTable>>
) TempTab
WHERE RowNum = 1

Open in new window

If you run the inner code alone, you will get an easy picture on how it is working. This method may have lesser reads compared to MAX() comparison or a SELECT in SELECT clause

Anoo

Brendt Hess

Note: I did not see YZLat's very similar code. The biggest change between our approaches is that I only return the ID and Count from the inner query, slightly reducing the data manipulation needed.

YZlat

Bhess isn't it what I posted?

PortletPaul

there is a difference, one returns OrderNumber and the subsequent join includes this, the other does not
----

It would be interesting to have the actual DDL for the data (including indexes) because there are very slight differences in execution plans between the similar approaches of YZlat, bhess1 and the approach I would also propose that Anoo already has.

here's a simple comparison of the 3
http://sqlfiddle.com/#!6/1c4c0/5

you can see execution plans there, notably bhess1's approach does not produce a lazy spool
but there are no indexes and the data volume is tiny

no points please

Anoo S Pillai

@Paul Maxwell - A big + for your approach, That distinguishes an expert.

Let me confess that I am not that much familiar with SQLFiddle, now one more item to explore.

earwig75

ASKER

Thank you for all of the great responses.