[sql] who's the youngest username ?

[PROKA]

Ok so I have this table named "Users" with the fields username and age

Im interested to know whos the youngest username.
How do I formulate the SELECT statement ?

 

[michael_hk]

If you are using SQL Server...

Select username from Users where age = (Select min(age) from Users)

 

[kejpa]

Select username from Users where age = (Select min(age) from Users)

Subselect, not too good. Use an aggregation instead:

SELECT username, Min(age) AS YoungestMember
FROM Users
GROUP BY Users.age;

/Kejpa

 

[cpopham]

Quick question, I understand that the subselect statement my add a small amount of overhead time to the server, but when you are talking about a Select statement this small how much time is it really wasting? I try to use the most efficient ways of doing things, but if you did not know another way of doing it, then what is wrong with the nested select statement? Just trying to learn some things here.

Chester

 

[Nerseus]

Heres another way to get the youngest person - this is SQL Server syntax, other languages use different syntax:

SELECT TOP 1 username, Age AS YoungestMember FROM Users
ORDER BY Age ASC

kejpas method works if you want all people, by age - not just the youngest. The subselect method works fine though you cant control which user you get back other than one - more or less randomly - from the users that match the youngest age. If 32 people are all 14 years old then youll get one of the 14, but you cant control which. The "TOP 1" with ORDER BY will let you have a little more control, if its needed.

Performance of each? Kinda depends on the amount of data you have and disbersement of values. The ORDER BY is going to want to try to use an index, as will the MIN() on the subselect. Those types of operations will vary depending on the data being looked at - in this case Age. The SQL engine has an Optimizer that evaluates all the SQL and creates a plan for how it thinks it can get to what you want the fastest. If youve got on the order of a few thousand or less users youll probably be fine.

-ner

 

[michael_hk]

kejpas method works if you want all people, by age - not just the youngest. The subselect method works fine though you cant control which user you get back other than one - more or less randomly - from the users that match the youngest age. If 32 people are all 14 years old then youll get one of the 14, but you cant control which. The "TOP 1" with ORDER BY will let you have a little more control, if its needed.
-ner

The subselect method

Select username from Users where age = (Select min(age) from Users)

will return all (32) users who are 14.

 

[Nerseus]

Youre right, my goof - I think I was thinking he still wanted just one username and that even with a sub-select hed still want a TOP 1 or similar.

-ner