Solved

Limit SQL Query results

Posted on 2010-11-09
2
356 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 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