I Have Table 'Sales' Where Orderno Is Uniquely Generated..Single Orderno Conatins Multiple Sale..Now I Want To Fetch Last 5 Orderno

Question

1.00/5 (1 vote)

See more:

My problem is how to fetch last 5 orderno..In my table 1 orderno contain multiple sales details.

for example..
I have 5 order in a day..
in 1st orderno, i have 5 sale
in 2nd order, i have 2 sale
in 3rd order, i have 1 sale
in 4th order, i ahve 7 sale
in 5th order, i have 3 sale

now, if i will fetch last 5 order then table will show (5+2+1+7+3)=18 no of record..
plz help me to write sql the query of the above example..

Thanks in Advance

Posted 13-Nov-14 21:22pm

itsathere

Add a Solution

Comments

Shweta N Mishra 14-Nov-14 3:28am

you mean you only want latest 5 records? i.e. 5th order with 3 sale and 4th order with 7 sales but only 2 records to get.

itsathere 14-Nov-14 3:38am

i want last five orderNo details..1 orderno contain sigle or multiple sale

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Accepted Answer · 2014-11-13T21:50:00

Solution 2

You can use something like this.

Select OrderNumber,RowID=DENSE_RANK() over (order by Ordernumber desc)
into #temp
from OrderTable

select * from #temp where RowID<=5

Posted 13-Nov-14 21:50pm

Shweta N Mishra

Updated 13-Nov-14 21:53pm

v2

Comments

itsathere 14-Nov-14 5:04am

In my table fields are SaleID(Primary key), CustomerID, MachineID, OriginalPrice,
OrderNo(Unique generated by code), Salesdate, SalesPrice, Quantity, VAT_CST, Discount, Status

here 1 OrderNo may contaion multiple sale..now i want to fetch last 5 OrderNo..
here OrderNo will be repeated multiple or single times it depend on sale at a single time..If u have solution of my question then help me plz..

Thank u..

Shweta N Mishra 14-Nov-14 5:12am

does the above didnt work for you, you need to replace it to use your tables and columns, If you order number is a character, you can sort on SalesDate or whichever column which tells you that this is the latest ordernumber.

itsathere 14-Nov-14 5:40am

your query shows last 5 details of OrderNo but not showing multiple sales with same order..

Shweta N Mishra 14-Nov-14 5:55am

it works for me with below example

create table #OrderTable(OrderNumber int)
insert #OrderTable select 1
insert #OrderTable select 1
insert #OrderTable select 1
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 2
insert #OrderTable select 3
insert #OrderTable select 3
insert #OrderTable select 4
insert #OrderTable select 4
insert #OrderTable select 4
insert #OrderTable select 5
insert #OrderTable select 5
insert #OrderTable select 6
insert #OrderTable select 7
insert #OrderTable select 7
insert #OrderTable select 7
insert #OrderTable select 7
insert #OrderTable select 8

Select OrderNumber,RowID=DENSE_RANK() over (order by Ordernumber desc)
into #temp
from #OrderTable

select * from #temp where RowID<=5

itsathere 14-Nov-14 8:03am

thanks for your solution..