dkilby
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
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
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.
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
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
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
hope that makes sense and thank you for the help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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.