CODEWITHSUNDEEP
sql
Vision Khiyal
Vision Khiyal

Reputation: 3

SQL query result with unique rows

This is my table code:

create table Names(id int, Fname varchar(40), Lname varchar(40));
insert into Names values(1, 'Zuma', 'Zytaveon');
insert into Names values(2, 'Sara', 'Luqman');
insert into Names values(3, 'Zuma', 'Zytaveon');
insert into Names values(4, 'Rafe', 'Raphael');

If i run the query 

SELECT DISTINCT from Names 
WHERE Fname = 'Zuma' 
    AND Lname = 'Zytaveon';

i get the following out put.

1|Zuma|Zytaveon
3|Zuma|Zytaveon

I want to have Zuma Zytaveon only one time in my query results, So how can I get that. Thanks for your help!

Upvotes: 0

Views: 327

Answers (2)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Various possibilities exist to select one element from a result set of multiple elements:

  • Use the DISTINCT keyword to eliminate duplicates in the result set. This is done by first sorting, so takes O(N * Log N ) time (if no index available for the sort).
  • Use an aggregate function such as MIN or MAX, requiring O(N) time to read the entire result set.
  • Use the TOP 1 construct to return the first located row in O(1) time.

Choose one of these depending on the other business requirements of the problem.

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT  DISTINCT FName, LName
from    Names 
WHERE   Fname = 'Zuma' AND Lname = 'Zytaveon';

if you want to have the lowest ID, use MIN

SELECT  MIN(ID) ID, FName, LName
from    Names 
WHERE   Fname = 'Zuma' AND Lname = 'Zytaveon'
GROUP   BY FName, LName

Upvotes: 2

Related Questions