...

View Full Version : Complicated query - maybe SELECT DISTINCT?



deafdigit
10-10-2011, 12:37 PM
Hi everybody,


I have a problem. I need a query and I can't figure out how to write it. I figured maybe some of you clever guys can.

I have a table that looks remotely like this:


id type jnr hovedstol
6 1 20208 33489.00
8 1 20213 5744.00
9 3 20213 5744.00
10 4 20213 5744.00
13 2 20216 4555.00
14 3 20216 4555.00
15 4 20216 4555.00
16 1 20215 2681.00

As you can see, some jnr appear more than once but with different type.

My query needs to get me the rows distinct to jnr (so every jnr only comes once) and only the rows where type is the highest with the given jnrs.

So the results with the above table should be:


id type jnr hovedstol
6 1 20208 33489.00
10 4 20213 5744.00
15 4 20216 4555.00
16 1 20215 2681.00

Every jnr only comes once, and only the one where type is the highest.

My SQL-skills at this time only get me to where I'm thinking "Hmm it's probably something with SELECT DISTINCT..."

Can someone help me with this humdinga?


Thanks in advance,
deafdigit

BubikolRamios
10-10-2011, 04:48 PM
select max(id,),max(type),jnr
from table
group by jnr


see no logic for id result, but then again this should return what you want. Can't figure logic for last column.

Old Pedant
10-10-2011, 08:41 PM
No, Bubikol. That might give a result where the id and type don't match. You can't get them independently.



SELECT T.*
FROM table AS T,
( SELECT jnr, MAX(type) AS maxtype
FROM table
GROUP BY jnr ) AS S
WHERE T.jnr = S.jnr
AND T.type = S.maxtype

Old Pedant
10-10-2011, 08:44 PM
CAUTION: If there are any "ties" in your table, that won't work.

That is, if you had something like this

14 3 20216 4555.00
15 4 20216 4555.00
16 1 20215 2681.00
17 4 20216 3135.22

15 and 17 have the same jnr and same type, so you will get *BOTH* records in your output.

If you need to break the ties (perhaps by then selecting the record with the higher id?), let me know.

deafdigit
10-11-2011, 10:14 AM
Thank you Old Pendant (and Bubikol Ramios too).

The way I designed the table, I'm almost but certain there aren't any ties (fingers crossed).

I'll report back when I try the queries out.

deafdigit
10-12-2011, 11:51 AM
Okay so this works great, but I have one more thing I need implemented for the query.

I have another table that looks like this:



jnr slut
20165 0
20166 2
20167 1
20168 1
20169 1


I need the query you guys made me to cross-check the jnr field with this other table to see if the slut is set to 1. If slut is not set to 1 then it should not accept that row.

I hope I'm making sense cause I'm confusing myself just writing the question :D


I hope you guys can help me,
Thanks in advance,
deafdigit

Old Pedant
10-12-2011, 11:06 PM
Trivial.


SELECT T.*
FROM table AS T,
otherTable AS OT,
( SELECT jnr, MAX(type) AS maxtype
FROM table
GROUP BY jnr ) AS S
WHERE T.jnr = S.jnr
AND T.type = S.maxtype
AND T.jnr = OT.jnr
AND OT.slut = 1

deafdigit
10-14-2011, 12:16 PM
Great. Thank you very much, Old Pendant.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum