Request help with a query

Question

Request help with a query

jeff-860557

Old Hand

Points: 305
More actions
September 13, 2009 at 9:45 am

#133946

Hi, Can someone please help me with a query. I think it should be really simple but for the life of me I just can't figure it out.
The underlying table structure is:
ID-----Vehicle----------DueHours-------DueName
1 55 654.7 Engine Overhaul
2 55 543.6 Tyre Balance
3 55 987.4 Timing Belt
4 63 843.5 Engine Overhaul
5 63 432.2 Tyre Balance
6 63 1098.3 Timing Belt
What I need to return in the query is the single row for each vehicle which has the lowest due hours. I can get to the point where I have a single row with the lowest but it only has the vehicle and the due hours. Whenever I add in a select for the DueName as well I end up with three rows for each vehicle rather than the one I am after. I have tried inner joins, outer joins and swearing. The swearing made me feel better but did not seem to help the result.
Thanks
Jeff

Viewing 15 posts - 1 through 15 (of 49 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Like many jobs, it's really simple if you have the right tool. In this case the right tool is the ROW_NUMBER() function. By partitioning on the vehicle number, it starts the count all over again at 1 for each vehicle. Ordered by Duehours, the row with the least amount of Due Hours will always have a ROW_NUMBER() equal to 1.

Please take a look at how I set up the sample data with a script so just anyone can cut and paste it and be ready to go. If you will set up problems like that, you will find that people are much quicker to offer coded and tested solutions.

Let me know if you have any questions.

declare @sample table (ID int, Vehicle int, DueHours numeric(5,1), DueName varchar(30))

insert into @sample

select 1, 55, 654.7, 'Engine Overhaul' union all

select 2, 55, 543.6, 'Tyre Balance' union all

select 3, 55, 987.4, 'Timing Belt' union all

select 4, 63, 843.5, 'Engine Overhaul' union all

select 5, 63, 432.2, 'Tyre Balance' union all

select 6, 63, 1098.3, 'Timing Belt'

select * from @sample

-- here's the solution

;with cte as (select *,ROW_NUMBER() over(partition by vehicle order by dueHours) as seqID from @sample )

select * from cte where seqID = 1

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

jeff-860557 Old Hand Points: 305 More actions · Answer 2

Hi Bob,

Wow, Thank you soooo much. I ummmed and ahhed for over a week before relenting and asking for help. There are a couple of constructs in there I had never heard of (Partition and row number for example) but I'll pull it apart word by word to understand it all.

It works a treat. Now I can finish my project.

I'll ensure that I setup all future questions in the manner youd described. Sorry I'm still a jewbi but I'm getting there.

Thanks again. I really appreciate it.

Jeff

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You're welcome, Jeff. You will find "PARTITION" under ROW_NUMBER() in books online. If you do a generic search on "partition", it will take you to information regarding partitioning of tables which is quite a different subject. But just play with it for a minute using the sample I sent back and you will see what it does. Try taking it out and using Order By only.

Thanks for taking to heart my suggestion about how to post in the future. You did great explaining what you wanted to see and providing sample data, but I had to take a couple of minutes to massage your post into something I could test. We have to remind people that a cut and paste from a spreadsheet or a query grid into text is really of limited help to the volunteers here. So are lengthy verbal descriptions, in lieu of a picture of what the expected output should be from a given set of sample data. But , with adequate information (in the proper format) people can go to "guns-on" immediately and knock out a solution.

Best of luck to you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

From one "Jeff" to another, welcome aboard and I agree... pretty good job for a first post.

To follow up on Bob's good suggestion, take a look at the article at the first link in my signature below. It tells how to easily make test data like Bob did and a couple of other hints that will just about guarantee quick, fully tested answers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ravi4work SSC Veteran Points: 201 More actions · Answer 5

I do not know why such a long query has been posted. it can be solved like this:

select * from vehicle_det A

where duehours= (select min(duehours) from vehicle_det where vehicle= A.vehicle)

-- here vehicle_det is your table name.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

ravi4work (9/14/2009)
I do not know why such a long query has been posted. it can be solved like this:
select * from vehicle_det A
where duehours= (select min(duehours) from vehicle_det where vehicle= A.vehicle)
-- here vehicle_det is your table name.

A) Its a single query (hardly long)

B) This doesnt work for multiple vehicles

C) What if there is a tie in duehours ?

Clear Sky SQL
My Blog[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

... and mine runs faster.

Game, Set and Match 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Nabha SSCrazy Eights Points: 8534 More actions · Answer 8

ravi4work (9/14/2009)
I do not know why such a long query has been posted. it can be solved like this:
select * from vehicle_det A
where duehours= (select min(duehours) from vehicle_det where vehicle= A.vehicle)
-- here vehicle_det is your table name.

there are different ways to write the query and before SS2k5 I would have thought the above query. Once you know the power of Over by clause, Partition and row_number, the first thing which you think when you need an ordered data is this logic. Atleast I am experiencing that.

Ofcourse this is in addition to what Dave and Bob are saying 🙂

---------------------------------------------------------------------------------

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

We forgive you for agreeing with us, PP 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Nabha SSCrazy Eights Points: 8534 More actions · Answer 10

Thanks Bob 😛

---------------------------------------------------------------------------------

ravi4work SSC Veteran Points: 201 More actions · Answer 11

well, I think I had put my comments before properly analysing the solution presented by others.

thanks for the showing the correct reasons.

ravi4work SSC Veteran Points: 201 More actions · Answer 12

well, I think I had put my comments before properly analysing the solution presented by others.

thanks for the showing the correct reasons.

Jonathan SSC-Insane Points: 20427 More actions · Answer 13

Bob Hovious (9/14/2009)
We forgive you for agreeing with us, PP 🙂

I'm afraid I cannot agree. I'd still use something like this:

SELECT ID, Vehicle, DueHours, DueName

FROM @sample s

WHERE DueHours IN

(SELECT MIN(DueHours)

FROM @sample

WHERE Vehicle = s.Vehicle);

Because:

1) It preserves information when there are ties. Showing only one row in the result set removes what could be relevant information. In fact the CTE query given will return an arbitrary result in case of ties. If ties are to be broken, include some agreed-upon business logic to do so, e.g. PARTITION BY Vehicle ORDER BY DueHours, ID.

2) It works with versions of SQL Server older than SQL Server 2005.

3) It is no slower than the CTE.

--Jonathan

HowardW One Orange Chip Points: 29109 More actions · Answer 14

The thing is I suspect it would be slower with a large dataset. If you want to preserve both rows in the event of a tie, use rank() rather than row_number().

It might look uglier, but it's generally the cheapest solution for this problem.