Solved

Limit SQL Query results

Posted on 2010-11-09
2
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 56
How can I get the entire database script? 7 34
Assigning Database Principals to Database Roles 3 41
Can a Trigger trigger a Trigger? 4 47
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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…

752 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