Selecting only a single row per id

Question

I'd like to get back only one row per ID, even if there are multiple rows associated with an ID.

The scenario is that the data reflects a site selling land / properties, and that for each listing, there may be multiple images associated with each ID. I need only one.

Thank You.

As requested, sample data:

RowNumber   ID  UserID  CountryID   tbl_detailsID   Name    Description Price   AddressLine1    AddressLine2    City    State   Zip AddressGPS  Space   Amenities   Policies    URL DateEntered DateExpires NumViews    IsCompanyProperty   EnforceMinStay  IsOtherSite rate_Avg    isFeatured  DayPrice    ImageURL    Name    check_in    check_out   currency    bathroom_count  pet unit_size   elder   handicap    occupancy   bedroom_count   LinkURL smoking unit_size_Units property_Type   children    Minimum_stay    Minimum_stay_Unit   CountryName
2   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-007-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
3   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-006-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
4   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-005-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
5   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-004-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
6   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-003-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
7   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-002-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States
8   247586  1   1   161398  Beautiful Oceanfront Home in Cambria California Located on the Central Coast of California 10 minutes from Hearst Castle.
Phone 1: Toll Free (800) 240-2277
Phone 2: (805) 927-0306 (California, USA)
direct email contact: debbie (@) maisonsdecambria.com

Leopold Cove" is a beautiful Oceanfront Home located on the Central Coast of California. It has fabulous ocean views and access to the water from a private stairway. The living area has large plate glass windows that feature 180 degree views of the Pacific Ocean. A comfortable platform style bed & a warm, cozy fireplace are also located in the living area so you can relax for hours, or even days, enjoying the beauty that surrounds you. There is a full kitchen & a separate bedroom with 2 twin beds. The property also has a picnic area & places to sit, perhaps with a good book & a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals & birdlife that we are privileged to have so close to us. During the gray whale migration times, this is a p     2183 Sherwood Drive     Cambria California  93428   35.544712|-121.095444               http://www.flipkey.com/cambria-vacation-rentals/p202618/    2012-10-01 10:52:00 2012-10-15 10:52:00 0   0   0   703 5.0 0   NULL    http://images1.flipkey.com/img/photos/302459/243298/large_302459-243298-001-1346943508.jpg  Beautiful Oceanfront Home in Cambria California         USD 1   Ask 0   Ask Ask 4   1   http://www.flipkey.com/cambria-vacation-rentals/p202618/    Ask     House   Ask 0   NULL    United States

And the Omni-query I that I am constructing to replace a few hundred lines of temp tables:

SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY tbl_Properties.ID DESC) AS [RowNumber], tbl_Properties.ID, tbl_Properties.UserID, tbl_Properties.CountryID, tbl_Properties.tbl_detailsID, tbl_Properties.Name, 
tbl_Properties.[Description], tbl_Properties.Price, tbl_Properties.AddressLine1, tbl_Properties.AddressLine2, tbl_Properties.City, tbl_Properties.[State], tbl_Properties.Zip, tbl_Properties.AddressGPS,
 tbl_Properties.[Space],
 tbl_Properties.Amenities,
 tbl_Properties.Policies,
 tbl_Properties.URL,
 tbl_Properties.DateEntered,
 tbl_Properties.DateExpires,
 tbl_Properties.NumViews,
 tbl_Properties.IsCompanyProperty,
 tbl_Properties.EnforceMinStay,
 tbl_Properties.IsOtherSite,
 tbl_Properties.rate_Avg,
 tbl_Properties.isFeatured,
 tbl_Properties.DayPrice,
 tbl_Images.ImageURL,
 tbl_Details.Name,
 tbl_Details.check_in,
 tbl_Details.check_out,
 tbl_Details.currency,
 tbl_Details.bathroom_count,
 tbl_Details.pet,
 tbl_Details.unit_size,
 tbl_Details.elder,
 tbl_Details.handicap,
 tbl_Details.occupancy,
 tbl_Details.bedroom_count,
 tbl_Details.url As [LinkURL],
 tbl_Details.smoking,
 tbl_Details.unit_size_Units,
 tbl_Details.property_Type,
 tbl_Details.children,
tbl_Details.Minimum_stay,
tbl_Details.Minimum_stay_Unit,
 tbl_Countries.Name As [CountryName]
 FROM [tbl_Details] 
 INNER JOIN [tbl_Properties] ON [tbl_Details].[detailsID] = [tbl_Properties].[tbl_detailsID]
 INNER JOIN [tbl_Images] ON [tbl_Images].PropertyID = [tbl_Properties].ID
 INNER JOIN [tbl_Countries] ON [tbl_Countries].ID = [tbl_Properties].CountryID 

The query itself is, of course, not finished yet.

Answer 1

If your SQLServer version supports row_number() OVER (2008,2012 support)

select * from 
(select t.*, 
  row_number() over (partition by id order by id) as rownumber from t
) t1
where t1.rownumber=1

Here is the SQLFiddle example

Answer 2

In SQL Server 2005+ you can use this

use tempdb
go

create table tbl (id int, s varchar(50))
insert into tbl values (1,'qwe'),(1,'asd'), (1,'qwff'),(2,'ert'),(2,'ykf'),(3,'')

;with cte
AS
(
SELECT id,s, ROW_NUMBER() OVER (PARTITION BY id ORDER BY s ) n
FROM tbl
)
SELECT id,s
FROM CTE
WHERE n=1

drop table tbl

Answer 3

try this:

SELECT TOP 1 * FROM
imagestable
WHERE imagestable.landID = <theID>

This will return the first image in the table that belongs to the property.

You can INNER JOIN this query with your property table if you want to return all property info as a single record.

Answer 4

Since there's no sample data you'll need to bend this response to suit your needs

select 
  Listings.*, SingleImage.Column1, SingleImage.Column2
from Listings
  cross apply (
    select top 1 *
    from Images
    where Listings.ListingID = Images.ListingID
    ) SingleImage

That needs SQL 2005 or newer (for the "cross apply" to work)

There are ways to make it work in SQL 2000 too but they're not as efficient