Solved

Limit SQL Query results

Posted on 2010-11-09
2
347 Views
Last Modified: 2012-08-13
This SQL query returns 5 records:

select FULLNAME,L_LAXON
from sde.HILLSBOROUGH_POLYLINE
where fullname like '% VARN%' or FULLNAME like 'VARN%'
GROUP BY FULLNAME,L_LAXON

I also want the ObjectID field returned but when I include it in the Select and Group By it increases the number of records returned to 22 and now I have duplicates of the FULLNAME and L_LAXON data.
How can I query for this information and get want I want, which is the 5 records including the OjbectID field.  Thanks!
0
Comment
Question by:MEINMEL
2 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 34096858
two options

select FULLNAME,L_LAXON, max(ObjectID)
from sde.HILLSBOROUGH_POLYLINE
where fullname like '% VARN%' or FULLNAME like 'VARN%'
GROUP BY FULLNAME,L_LAXON

or this other one

select FULLNAME,L_LAXON, ObjectID
from (
 select FULLNAME,L_LAXON, ObjectID, row_number() over (partition by FullName, L_LAXON order by FULLNAME, L_LAXON) rn
 from sde.HILLSBOROUGH_POLYLINE
 where fullname like '% VARN%' or FULLNAME like 'VARN%'
) a
where rn = 1
 
0
 

Author Closing Comment

by:MEINMEL
ID: 34096902
Thank you so much, especially for the fast and accurate response!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now