Link to home
Start Free TrialLog in
Avatar of dkilby
dkilbyFlag for Canada

asked on

ms sql + get number in list out of total

is there anyway to show the number of an order in a list of orders. 3 out 17 for example

so the table has order number and orderdate, i want to run the query with a where statement of an ordernumber, pulling hte list newest to oldest, then have a column that would show what number each row is out of the total rows in the query.

For example

Order Number   Modify Date  Order #
123456                 1/11/2017   3/3
123456                 1/10/2017   2/3
123456                 1/8/2017     1/3

Thanks
Avatar of James Lee
James Lee

SELECT *
FROM `Orders`
ORDER BY (`Order#` = '123456') DESC, `Order#`

I assume here that the table is named "Order" and that your trying to pull a specific order number as in "123456" and then sort that by the last column "Order #".  I hate # in a field name by the way.  Field names need not be too descriptive. The report can be changed to say what the field name means when you run the report or whatever.
Avatar of dkilby

ASKER

sorry was not clear, i know how to pull the list but when i pull the  list I want to add a column that shows the number out of total orders in the list.
Avatar of Scott Pletcher
I don't have data to test it, but something like this should do it:

SELECT tn.[Order Number], tn.[Modify Date],
    CAST(ROW_NUMBER() OVER(ORDER BY tn.[Order Number], tn.[Modify Date]) AS varchar(5)) + '/' +
    CAST(COUNT() OVER() AS varchar(5)) AS [Order #]
FROM table_name tn
Avatar of dkilby

ASKER

Scott this works perfectly for one order, if I wanted to run this query for the whole table, but have the numbering be at the order level, so 1 order might have 12 entries, next order 15 and the count would reset in a big list by order?

hope that makes sense and thank you for the help
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dkilby

ASKER

perfect - thank you
Scott has your answer, you just may want to add an order by.  Here is a working example with data and the output.
CREATE TABLE table_name ([Order Number] INT, [Modify Date] DATETIME);
INSERT INTO dbo.table_name ( [Order Number], [Modify Date] )
VALUES  
(123456, '1/11/2017'),
(123456, '1/10/2017'),
(123456, '1/08/2017'),
(987654, '12/12/2016'),
(987654, '12/11/2016'),
(987654, '12/10/2016'),
(987654, '12/08/2016')

SELECT tn.[Order Number], tn.[Modify Date],
    CAST(ROW_NUMBER() OVER(PARTITION BY tn.[Order Number] ORDER BY tn.[Modify Date]) AS varchar(5)) + '/' +
    CAST(COUNT(*) OVER(PARTITION BY tn.[Order Number]) AS varchar(5)) AS [Order #]
FROM table_name tn
ORDER BY tn.[Order Number], tn.[Modify Date] DESC;

DROP TABLE dbo.table_name;

Open in new window

User generated image