SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORDER BY


ORDER BY

Author
Message
2Tall
2Tall
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1164 Visits: 1203
Hi. I posted previously and NULL kindly provided a solution. Part of the code was as follows:

SELECT ProductId
,COUNT(1) OrderCount
,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/MANCount ELSE AVG(MAN) END MAN
,CASE WHEN COUNT(1) = 1 THEN SUM(PUR)/PURCount ELSE AVG(PUR) END PUR
FROM (SELECT DENSE_RANK() over (partition by ProductId order by OrderId desc) as OrderRank



Part of the solution was to return the average cost for the last 5 orders of a Product. However sort desc the last 5 orders listed are:

WO9901902
WO9901745
WO9901611
WO9901532
WO9901424

When in fact the last 5 orders appear in the middle?
WO014563
WO014377
WO014378
WO014440
WO014268

Will it be possible to return the last 5 orders with the absense of a date field?

Sample data attached for a single product with multiple orders.
Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Attachments
OrderID.zip (12 views, 9.00 KB)
Ramesh Saive
Ramesh Saive
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3468 Visits: 2643
Philip Horan (1/13/2009)

When in fact the last 5 orders appear in the middle?
WO014563
WO014377
WO014378
WO014440
WO014268

How could you tell that these are the last 5 orders?

Will it be possible to return the last 5 orders with the absense of a date field?

Off course yes, but you have tell SQL that this is the column that identifies the order of the records.

If you meant that the order it was entered into the database then you have to know that rows in a table does not have a specific order.

--Ramesh


2Tall
2Tall
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1164 Visits: 1203
Hi Ramesh. Having reveiwed the orders from within the existing system it came to light that what I thought was the last OrderId was not in fact the case, they just appeared to be........

I can see of no way to achieve what I have asked but I asked anyhows Smile (lack of date field does not help!).

Thanks for taking a look.
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1671 Visits: 7894
In the data you have provided the OrderId looks as though it may contain a 2 digit year.
If this is the case then something like the following may work:

ORDER BY
    CASE
        WHEN SUBSTRING(OrderID, 3, 2) < '10'
        THEN '20' + RIGHT(OrderID, 7)
        ELSE '19' + RIGHT(OrderID, 7)
    END


Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18474 Visits: 14895
I think Ken is on the right track, in that we need to know how the OrderId is contructed.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
2Tall
2Tall
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1164 Visits: 1203
Thanks guys. I will need to check with the app vender.

It has come to light that I may be able to get what I am after by extracting from a second dataset (logs stock movements). The dataset in question lists ProductId multiple times with a date the stock moved (some products moved multiple times on the same day). How might I construct a query that returns the last 5 dates per product?

The dataset in question has 200000 records (lots of repatition).

I would name the table StockMovements and it would ahve 2 fields 'ProductId', 'DateMoved'.
Many Thanks,
Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search