• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

Need help with a query. Probably a self join or a nested query.

I am having difficulty with a query. I have tried a nested query and
self join, but just can't get it. So possibly someone can help me out?

I have a table below and obviously it has redundant data. basically all of
the codes are associated with a description. I don't care which description
is returned from my query. I only want a unique code, and the description
associated with the unique code. Can someone help me out? Below is my table
Apartements and below that is the resulting query of data I would like returned.

Table Name "Apartements"

Code     Description
TB       Two Bedroom 1 bath
TB       Two Bedroom
TB       Two Bedroom plus patio
OB       One Bedroom
OB       One Bedroom plus sink
OB       One Bedroom plus 1 bath
FB       Four Bedroom
FB       Four Bedroom plus 2 bathrooms
RU       One night rental unit
RU       One Night with breakfast

Resulting Query :

TB      TWo Bedroom 1 bath
OB      One Bedroom
FB      Four Bedroom
RU      One night with breakfast
  • 2
1 Solution
SELECT code, max(Description) as Description
FROM Apartements

Open in new window

SELECT code, Description FROM (
SELECT code, Description, row_number()over(partition by code order by Description) rn
FROM Apartements)v
WHERE rn = 1

Open in new window

brgdotnetAuthor Commented:
Thank you so much !!!!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now