July 14, 2015 at 12:27 pm
Hello people, this might be very simple but I'm having a hard time trying to get this to work:
I have the following table:
Name Value
Xavier4
Abigail4
Muriel 4
Xavier3
Abigail3
Xavier2
Muriel 1
What I need to get is the DISTINCT Names and each one's MIN Value
Name Value
Xavier2
Abigail3
Muriel 1
Can anyone help me?
July 14, 2015 at 12:33 pm
You just need to use MIN() and GROUP BY.
What have you tried? What problems have you encountered?
July 14, 2015 at 12:47 pm
Even The Management studio was trying to say this to me
"Column 'NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Thanks a lot, now I have to do a subquery on this, I've been working with DISTINCT in this table, and seems like I forgot how MIN works.
Thanks again,
XC
July 14, 2015 at 12:57 pm
xanuyo (7/14/2015)
Even The Management studio was trying to say this to me"Column 'NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Thanks a lot, now I have to do a subquery on this, I've been working with DISTINCT in this table, and seems like I forgot how MIN works.
Thanks again,
XC
Is there something else that you didn't share with us? I don't see the need for a subquery for what you posted.
I'm not sure if you figured it out, but you don't need to use DISTINCT when you're using GROUP BY.
July 14, 2015 at 1:03 pm
Luis, this is a subquery I needed to complete for a bigger query, your answer helped me since I was trying to use DISTINCT with MIN when I needed to just use GROUP BY and MIN, I got this working and the bigger query too.
Thanks again,
XC
July 14, 2015 at 1:16 pm
That's great! I just wanted to make sure that you weren't over complicating yourself.
I'm glad that you solved your issue.
July 14, 2015 at 2:36 pm
1 extra question, I have to add an extra Column from the original table thats not related to the 'Value' Column.
Supose the original Table is:
NameValueSS
Xavier42
Abigail43
Muriel42
Xavier33
Abigail32
Xavier22
Muriel13
Now that I have the proper MIN values from:
SELECT a.Name, MIN(a.Value)FROM Tab1 a
GROUP BY a.Name
I need:
NameValueSS
Xavier22
Abigail32
Muriel13
The only way to do this is with a Subquery?
SELECT b.Name, b.MIN_VAL, c.SS FROM
(SELECT a.Name, MIN(a.Value) AS MIN_VAL FROM Tab1 a
GROUP BY a.Name) b
INNER JOIN Tab1 c
ON b.Name = c.Name AND b.MIN_VAL = c.Value
Or is there a better and less expensive way to do this?
July 14, 2015 at 2:45 pm
xanuyo (7/14/2015)
1 extra question, I have to add an extra Column from the original table thats not related to the 'Value' Column.Supose the original Table is:
NameValueSS
Xavier42
Abigail43
Muriel42
Xavier33
Abigail32
Xavier22
Muriel13
Now that I have the proper MIN values from:
SELECT a.Name, MIN(a.Value)FROM Tab1 a
GROUP BY a.Name
I need:
NameValueSS
Xavier22
Abigail32
Muriel13
The only way to do this is with a Subquery?
SELECT b.Name, b.MIN_VAL, c.SS FROM
(SELECT a.Name, MIN(a.Value) AS MIN_VAL FROM Tab1 a
GROUP BY a.Name) b
INNER JOIN Tab1 c
ON b.Name = c.Name AND b.MIN_VAL = c.Value
Or is there a better and less expensive way to do this?
I don't know if there's a better way, but just in case you ever have multiple occurrences of the same minimum value, you might need to add DISTINCT to the outer query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 3:24 pm
Typically the ROW_NUMBER() method will outperform other methods. It also guarantees only a single result row, which means that duplicate values will be ignored (I believe you can use DENSE_RANK to change that if you need to):
SELECT derived.Name, derived.Value, derived.SS --,...
FROM (
SELECT Name, Value, SS, --,... other column(s) if needed
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Value) AS row_num
FROM tabl
) AS derived
WHERE
derived.row_num = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2015 at 4:16 pm
It's super effective!
Worked like a charm, thanks a lot.
XC
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply