Databases
StackOverflow2013
Postsdbo
POSelecting only a single row per id
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting only a single row per id
    primarykey
    data
    text
    <p>I'd like to get back only one row per ID, even if there are multiple rows associated with an ID.</p> <p>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.</p> <p>Thank You.</p> <p>As requested, sample data:</p> <pre><code>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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 &amp; 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 &amp; a separate bedroom with 2 twin beds. The property also has a picnic area &amp; places to sit, perhaps with a good book &amp; a glass of wine while you watch the sunset. If you are a nature lover, you will enjoy the otters, seals &amp; 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 </code></pre> <p>And the Omni-query I that I am constructing to replace a few hundred lines of temp tables:</p> <pre><code>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 </code></pre> <p>The query itself is, of course, not finished yet. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser978122
    1. USuser978122
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelecting only a single row per id
      1. This table or related slice is empty.
      1. VTUpMod
    1. COplease provide sample data.
      singulars
      1. POSelecting only a single row per id
      1. USJohn Woo
    2. COwhich sql server are you using? and sample data would help to give proper solution
      singulars
      1. POSelecting only a single row per id
      1. USAnandPhadke
    3. COWont Limit clause work in this scenario ?
      singulars
      1. POSelecting only a single row per id
      1. USAjith Sasidharan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload