CODEWITHSUNDEEP
sqlms-access
Natalia Fontiveros
Natalia Fontiveros

Reputation: 115

my query returns many records per id, I just one any of them per id

Hi friends from Stack Overflow. I am trying to run a query in MS Access, i just want one record per ID randomly, but I am getting all.

I tried with distinct is not working, and I try with top, didn't work either.

this is my table below originaldata

ID  HotelName Role Email
__________________________
1  bandb  admin [email protected]
1  bandb  admin [email protected]
1  bandb  admin [email protected]
1  bandb  user  [email protected]
2  myhtl  admin [email protected]
3  ben    admin [email protected]
3  ben    user  [email protected]
4  moon   admin [email protected]
4  moon   admin [email protected]

I want to get the below results

ID  HotelName Role Email
__________________________
1  bandb  admin [email protected]
2  myhtl  admin [email protected]
3  ben    admin [email protected]
4  moon   admin [email protected]


SELECT  *
FROM OriginalData
WHERE (((OriginalData.[Role])='admin') AND ((OriginalData.[ID]) In (Select Distinct [ID] from [OriginalData] where [Role] = 'Admin'  )));

Thank you for your time and help

Upvotes: 0

Views: 64

Answers (3)

yu2
yu2

Reputation: 126

SELECT ID, min(HOTELNAME), min(ROLE), min(EMAIL)
from OriginalData
group by ID

Upvotes: 1

John
John

Reputation: 1004

Well this is easy but with a twist (notice the the TOP 1...it will return just one record

SELECT TOP 1 *
FROM OriginalData
WHERE OriginalData.[Role])='admin'

The problem is that in order to get Random you need to use something for randomize it for some value ...here lets take ID
So the SQL will be : 

SELECT TOP 1 *
    FROM OriginalData
    WHERE OriginalData.[Role])='admin' AND ID = Clng(Rnd() * Dmax("ID","OriginalData")

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

Assuming you would be OK with displaying the "minimum" email address per each hotel group, then the following query should work:

SELECT od1.ID, od1.HotelName, od1.Role, od1.Email
FROM OriginalData od1
INNER JOIN
(
    SELECT ID, Hotel, MIN(Email) AS min_email
    FROM OriginalData
    WHERE Role = 'admin'
    GROUP BY ID, Hotel
) od2
    ON od1.ID = od2.ID AND
       od1.Hotel = od2.Hotel AND
       od1.Email = od2.min_email
WHERE
    od1.Role = 'admin'
ORDER BY
    od1.ID;

Edit:

Coincidentally, for the exact data you showed us, you might also be able to simplify to:

SELECT ID, Hotel, Role, MIN(Email) AS Email
FROM OriginalData
WHERE Role = 'admin'
GROUP BY ID, Hotel, Role;

However, this only works because all the columns you want to appear are either aggregates or constants. If you had other columns specific to a matching minimum row, this would not work.

Upvotes: 0

Related Questions