Double Select MIN()
-
SELECT MIN(Col1) AS One, MIN(Col2) as Two ...
will return the minimum Col1 and the minimum Col2
But what I want is minimum Col2 where Col1 is minimum.
For example I have this table:
ID Col1 Col2
1 1 2
2 1 3
3 1 4
4 2 1
5 2 2
The select will return 1 1, while I'm interested in the row with the id 1, 1 2
I know how to achieve this using a subselect, but I wonder if it's possible to do it in just one select.
-
I can't figure out a straight select way to do this. The closest I can come with is like you said :
DECLARE @demo TABLE (ID INT NOT NULL IDENTITY(1,1), Col1 INT NOT NULL, Col2 INT NOT NULL)
INSERT INTO @demo (Col1, Col2)
SELECT 1, 2
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 2, 2
UNION ALL
SELECT 2, 0 --testing to see if the query works all the time
SELECT * FROM @demo
SELECT MIN(Col1) AS Col1, MIN(Col2) AS Col2 FROM @demo WHERE Col1 = (SELECT MIN(Col1) FROM @demo)
But then again you'd need to join back to the base table to get the rest of the column. In all cases however, you'd ahve to do that anyways.
-
Thank you for your reply. I just wanted to make sure that theres no other way, because in the real case I also have some inner joins and a where clause, that sadly I have to do for both of the selects.
-
Can you post the actual query? Maybe we can figure something out with derived table or cte or??
-
SELECT MIN(Kills.KillId) AS KillId, FirstKills.BossId
FROM Kills
INNER JOIN ( SELECT MIN(Date) AS Date, BossId
FROM Kills
INNER JOIN Guilds ON Guilds.GuildId = Kills.GuildId
INNER JOIN Realms ON Realms.RealmId = Guilds.RealmId
WHERE Date IS NOT NULL
AND Guilds.Faction = 'Alliance'
AND (@Zone = 'Worldwide' OR Realms.Zone = @Zone)
AND (@Language = 'Any' OR Realms.Language = @Language)
GROUP BY BossId ) FirstKills ON FirstKills.Date = Kills.Date AND FirstKills.BossId = Kills.BossId
INNER JOIN Guilds ON Guilds.GuildId = Kills.GuildId
INNER JOIN Realms ON Realms.RealmId = Guilds.RealmId
WHERE Guilds.Faction = 'Alliance'
AND (@Zone = 'Worldwide' OR Realms.Zone = @Zone)
AND (@Language = 'Any' OR Realms.Language = @Language)
GROUP BY FirstKills.BossId
That's the part of the query with the issue. It's working as intended now. I hope you can understand it
-
Can you post a script to create the tables and some sample data along with the required results. Maybe there's a better way but I can't sese one at the moment... but if I had the tbales ot work with, then I could run some tests.
-
WOW! I'm thinking that a select top 1, where you order by in the sequence you want to control by - but what if you have more than one minimum value match? If you are only controlling to find the values, then select top 1 should work, no?
something like:
SELECT top 1 Kills.KillId AS KillId, Kills.Date, FirstKills.BossId
FROM Kills
INNER JOIN FirstKills ON FirstKills.Date = Kills.Date AND FirstKills.BossId = Kills.BossId
INNER JOIN Guilds ON Guilds.GuildId = Kills.GuildId
INNER JOIN Realms ON Realms.RealmId = Guilds.RealmId
WHERE Guilds.Faction = 'Alliance'
AND (@Zone = 'Worldwide' OR Realms.Zone = @Zone)
AND (@Language = 'Any' OR Realms.
order by Kills.Date, Kills.KillId
-
These are max() functions, but same thing. You may be able to do it, but will it really be more efficient?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=387101#bm389305
-
It is the ROW_NUMBER() example in the above link which is likely to be the most efficient. eg
SELECT D.BossID, D.KillID
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY BossID ORDER BY KillID) AS RowID
,Kills.BossId
,Kills.KillID
,Kills.[Date]
,Kills.GuildId
,Kills.RealmId
-- etc
FROM Kills
INNER JOIN Guilds ON Guilds.GuildId = Kills.GuildId
INNER JOIN Realms ON Realms.RealmId = Guilds.RealmId
WHERE Kills.[Date] IS NOT NULL
AND Guilds.Faction = 'Alliance'
AND (@Zone = 'Worldwide' OR Realms.Zone = @Zone)
AND (@Language = 'Any' OR Realms.Language = @Language)
) D
WHERE D.RowID = 1
-
declare @Table table (
id int primary key,
col1 int,
col2 int
);
-- Mix up the values so there is no beneficial order
insert into @Table
(id, col1, col2)
select 1, 1, 3 union all
select 2, 2, 2 union all
select 3, 1, 4 union all
select 4, 1, 2 union all
select 5, 2, 1;
select top 1 *
from @Table
group by id, col1, col2
order by col1, col2
-- Result:
-- id col1 col2
-- 4 1 2
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
So you want the smallest MIN(Col2) where Col1 = MIN(Col1) right?
Why not
SELECT Col1, MIN(Col2)
FROM Table
WHERE Col1 = (SELECT MIN(Col1) FROM Table)
GROUP BY Col1
-
As I said I know how to do it with 2 selects, but I was wondering if its possible to do it with just one select. I have to inner join some big tables and and create a where clause, so having 2 selects means I have to do this for each select.
Now I found that theres no way to do it in 1 select, so I have to stick with the solution I already have.
Thanks for all your replies. You all were a great help and gave me some good ideas.
-
Um, I did it in one select, based on the example you posted.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply