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
earwig75Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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

YZlatCommented:
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

Mike EghtebasDatabase and Application DeveloperCommented:
Correction:
Select D.ID, D.[Order Number]
      , (Select Count([Order Number]) From Table1 t 
         Where t.[Order Number]=D.[Order Number]) As [Count]
      , D.SomethingElse
From(Select  
       row_number() Over(Partition By ID Order By ID Desc) As rn
     , ID
     , [Order Number]
     , [Date] As OrderDate
     , SomethingElse
From Table1) As D (rn, ID, [Order Number], SomethingElse)
Where rn = 1

Open in new window


This post has been modified...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

YZlatCommented:
forgot the spacing in Order Number

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
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 HessSenior DBACommented:
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 PillaiCommented:
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 HessSenior DBACommented:
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.
YZlatCommented:
Bhess isn't it what I posted?
PortletPaulEE Topic AdvisorCommented:
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 PillaiCommented:
@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.
earwig75Author Commented:
Thank you for all of the great responses.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.