How Do I Omit Repeating Results

Question

Let’s say you are an employee at a business where they put employees in a database, with the columns ID, FIRSTNAME, LASTNAME column. Now, if you have multiple last names, what SQL technique do you use to change the output from this:

ID            FIRST NAME       LASTNAME
1             James            Bond
1             James            Carnegie 
1             James            Rockefeller

To this:

ID            FIRST NAME       LASTNAME
1             James            Ferguson
                               Carnegie 
                               Rockefeller

Basically omitting the need to repeat the non-changing information for every changing last name, to make a better list when outputting it to a website.

I don't think you can do that as you describe. (Academic reason - the output of a SELECT query is a relation, and a relation must have the same number of columns in each row) — Eric Petroelje, Jul 17 '13 at 19:07
usually, you do this using a "group by" clause, but I will have trouble helping you formulate one without a starting query. — PaulProgrammer, Jul 17 '13 at 19:05
@Pete Belford This is just an example, it wouldn't have the same ID...I'm asking, what if this hypothetical person is named James Bond Carnegie Rockefeller (there all his name so there is only one ID), how do I get it to print his ID and name only once, while printing out ALL of his last names in list. — UndefinedReference, Jul 17 '13 at 19:17
I would consider that the persistence layer is maybe not the best place to handle this feature, since it seems more like a presentation concern. This seems like view- or controller-level logic to me. — Jason Swett, Jul 17 '13 at 19:40
possible duplicate of [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) — Clockwork-Muse, Mar 02 '14 at 05:33

score 1 · Answer 1 · answered Jul 17 '13 at 19:19

select case
          when row_number() over (partition by id order by id) = 1 then id
          else null
       end as id,
       case 
          when row_number() over (partition by firstname order by id) = 1 then firstname
          else null
       end as firstname,
       lastname
from the_table
order by id, firstname

It's not clear from your question if the same ID always has the same firstname, in that case it could be written a bit simpler using a single window definition:

select case
          when row_number() over id_window = 1 then id
          else null
       end as id,
       case 
          when row_number() over id_window = 1 then firstname
          else null
       end as firstname,
       lastname
from the_table
order by id, firstname
window id_window as (partition by id order by id)

Right, lets say there is one person who's ID is 1, and that ID also calls only one first name (Steve) but the ID is also tied to multiple last names (any few you can think of). When I go to return his ID, name and lastname, his ID and name appear for every value of the lastname with that associative ID. But that makes it look like he's several people instead of one person with many, many last names. — UndefinedReference, Jul 17 '13 at 19:32
@navlag: you should update your question to include those combinations in your sample data and output. But it sounds like my first query will do what you want. — , Jul 17 '13 at 19:33

score 0 · Answer 2 · answered Jul 17 '13 at 19:06

0

Well for one thing an ID should be an identifier and unique

If one person has multiple last names you should have some join table that holds all of those other last names that you can do a join query on.

Then you can just do something like

SELECT * FROM users
JOIN lastNames on users.id = lastNames.user_id;

answered Jul 17 '13 at 19:06

Naftali

144,921
39
244
303

Right but the thing is, in this hypothetical sitution, this person has this one ID, one FIRSTNAME, but several LASTNAMES. How to I print his name and ID once, next to a list of all of the LASTNAME fields that correspond with his ID? – UndefinedReference Jul 17 '13 at 19:19
1

@navlag use a group by... but please please please.... IDs should be unique... – Naftali Jul 17 '13 at 19:20
but it's the same person. – UndefinedReference Jul 17 '13 at 19:21
@navlag oy... please read my answer... – Naftali Jul 17 '13 at 19:21

shashankg77 · Answer 3 · 2013-07-18T02:44:10.433

0

Just use the unique keyword

SELECT distinct firstName from ------ where ------

edited Jul 18 '13 at 02:44

answered Jul 17 '13 at 19:11

shashankg77

2,206
4
18
34

but what if it's the same person with the same ID, but he just has multiple last names. How do I return all his last names without having to return his first name several times? – UndefinedReference Jul 17 '13 at 19:20
The SQL-standard form would be `SELECT DISTINCT ...` – Craig Ringer Jul 18 '13 at 02:38

score 0 · Answer 4 · answered Jul 18 '13 at 11:23

Just

select lastname from table where firstname ='SOMEONE'

then you will get all three lastname. What about the only one id and firstname? It's about how you do the reporting, not the query.

select id, firstname from table where firstname ='SOMEONE'

but then that would be two trips to database.

Better just

select id, firstname, lastname from table where firstname='SOMEONE'

How Do I Omit Repeating Results

4 Answers4