Delete dups

[tim.tait]

I have a table that contains duplicate userids but each user id has
another set of information that is put with a priority. eg they say
their favourite colours are blue and red. blue has a high priority than
red so we say that blue is their favourite colour. i was just wondering
how i can go through and pick a userid and their favourite colour
according to which one has the highest priority.

an example of the table is (1 is the highest priority where 3 is the
lowest here)

user table colour table
userid colourcode colourid colour
priority
0001 33 33 blue
1
0001 22 22 red
2
0001 11 11 green
3
0002 22 22 red
2
0002 11 11 green
3

sorry if its a bit confusing any help would b greatly apriciated

 

[tim.tait]

hope this is better

user table colour table
userid colourcode colourid colour priority
0001 33 33 blue 1
0001 22 22 red 2
0001 11 11 green 3
0002 22 22 red 2
0002 11 11 green 3

 

[Smartin]

I have a table that contains duplicate userids but each user id has
another set of information that is put with a priority. eg they say
their favourite colours are blue and red. blue has a high priority than
red so we say that blue is their favourite colour. i was just wondering
how i can go through and pick a userid and their favourite colour
according to which one has the highest priority.

an example of the table is (1 is the highest priority where 3 is the
lowest here)

user table colour table
userid colourcode colourid colour
priority
0001 33 33 blue
1
0001 22 22 red
2
0001 11 11 green
3
0002 22 22 red
2
0002 11 11 green
3

sorry if its a bit confusing any help would b greatly apriciated

In your example colour preference is a function of colour, not user, but
anyway this seems to work:

Create query UsersPriorities:
SELECT users.userid, Min(colours.priority) AS MinOfpriority
FROM users INNER JOIN colours ON users.colourcode = colours.colourid
GROUP BY users.userid;

Then create query UsersColours:
SELECT UsersPriorities.userid, colours.colourid, colours.colour,
colours.priority
FROM UsersPriorities INNER JOIN colours ON UsersPriorities.MinOfpriority
= colours.priority;