MySQL select distinct values based on specific field

Question

hi i have a table with 6 columns, amongst them the is a column called user_id which has many occurances in the table and also a column i.e laptops where it references the laptops that a user has brought for some action. This also have many occurances in the table.

ID  DATE        LAPTOP    USER_ID
1   1/1/2014    Acer      10
2   1/1/2014    Toshiba   2
3   1/1/2014    HP        5
4   1/1/2014    Acer      5
5   1/1/2014    Toshiba   5
6   1/1/2014    HP        2
7   2/1/2014    Lenovo    2
8   2/1/2014    Acer      10
9   2/1/2014    HP        2
10  2/1/2014    HP        5

i need to select the distinct laptop occurences for each user_id and not generally the distinct occurances frm the table so the desired result would be

LAPTOP    USER_ID
Acer      10
Toshiba   2
HP        2
Lenovo    2
HP        5
Acer      5
Toshiba   5

whatever i ve tried seems to be far from the desired result so far

Wouldn'tselect distinct laptop, user_id from tablegive you the desired result? — jpw
– jpw, Commented Nov 9, 2014 at 20:28
And that is exactly what you would get, distinct combinations of laptop and user_id. See sqlfiddle.com/#!2/340bc6/2 — jpw
– jpw, Commented Nov 9, 2014 at 20:31
@nikolas jpw's solution will work too, it will select distinct combination of laptop, user_id — CodeBird
– CodeBird, Commented Nov 9, 2014 at 20:32

CodeBird · Accepted Answer · 2014-11-10 09:40:04Z

2

you can use GROUP BY

SELECT LAPTOP, USER_ID FROM Table1 GROUP BY LAPTOP, USER_ID;

just to make the answer as complete as possible, I am adding jpw's solution that is a comment above.

SELECT DISTINCT laptop, user_id FROM table

Here's a GROUP BY fiddle

Here's jpw's DISTINCT fiddle

edited Nov 10, 2014 at 9:40

answered Nov 9, 2014 at 20:29

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nikolas Over a year ago

this workds as well..i guess it wasn't so complicated as initially thought...thank you very much

Collectives™ on Stack Overflow

MySQL select distinct values based on specific field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related