I'm looking for a little SQL help please

[Punt]

Hi,

I'm not much of a SQLer so looking for a little help.

Say I have the following table in SQL...

I'm trying to build a sql string that returns the first instance of each [Username] based on the lowest [Index].

The resulting table would be...

Many thanks

 

[SimonCHere]

Ive not tried it, but what about

select username, string, min(index)
from users
group by username

 

[planty]

Think this should do the trick...

SELECT Username, String, MIN(Index)
FROM Tablename
GROUP BY Username

GROUP BY will return a row for each username and when used with MIN it will return the record with the lowest index.


EDIT: beaten to it

 

[Punt]

Afraid not...

Error...

"Column 'String' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

 

[Poolybit]

Afraid not...

Error...

"Column 'String' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

You'll need to include the other column that isn't an aggregate function within your group statement (which shouldn't matter unless you have multiple entries for the same username with different string values):

SELECT Username, String, MIN(Index) 
FROM Tablename
GROUP BY Username, String

/Edit Just properly read your opening post...disregard the above! Will update shortly..

 

[Punt]

That returns everything.

 

[mark66]

You'll need to include the other column that isn't an aggregate function within your group statement (which shouldn't matter unless you have multiple entries for the same username with different string values):

SELECT Username, String, MIN(Index) 
FROM Tablename
GROUP BY Username, String

/Edit Just properly read your opening post...disregard the above! Will update shortly..

Not sure that will work either, he will now get one row per username, per string, so the results will look the same as the starting table.

You will need to use this:

SELECT Username, MIN(String), MIN(Index)
FROM Tablename
GROUP BY Username

 

[SimonCHere]

Will that work though? Will the min(string) override the min(index)?

how about

select username, string, index
from tablename
where index in (select min(index) from tablename group by username)

 

[skyripper]

Another way would be

SELECT * FROM Table A
WHERE A.index=(SELECT MIN(A1.index) FROM Table A1 WHERE A.Username=A1.Username))

 

[planty]

SELECT Tablename.username, Tablename.String, Tablename.Index 
FROM (
SELECT username, Min(Index) AS MinIndex 
FROM Tablename Group BY username) MinFiltered
INNER JOIN Entity ON Tablename.Index = MinFiltered.MinIndex;

This way you are filtering your min values and then joining on the index with the unfiltered table to get the correct string value (assuming index is unique).

 

[Punt]

Can't use MIN(String) as I can't (don't want to) test against the value of a string.

Another way would be

SELECT * FROM Table A
WHERE A.index=(SELECT MIN(A1.index) FROM Table A1 WHERE A.Username=A1.Username))

This seems to do the trick

Cheers everyone for their help.

 

[Phunky]

What's wrong with a basic order by?

SELECT * 
FROM user
GROUP BY `Username`
ORDER BY `Index` ASC

GROUP BY will match the first row for that user and the ORDER BY shorts the desired order.