66492

Distinct on only one column - only display FIRST duplicate row

Question:

I'm using an SQL query in PHP to display 5 columns from my "users" table. Here is my code for that:

SELECT DISTINCT account_id, full_name, email, login, phone, updated_at, last_request_at, unconfirmed_email FROM $table WHERE id < '300'

Basically, this table has 2 types of user - admin accounts and what we'll call "subusers" - they're still users but they don't have admin privileges, and they were created by their parent admin accounts. Because of this, the parent admin account and its subusers all share the same "account_id" Here's an example (sorry it's rubbish):

| account_id | full_name | Superhero? | | 1 | Batman | 1 | | 1 | Robin | 1 | | 1 | Magneto | 0 | | 2 | Spiderman | 1 | | 2 | The Hulk | 1 | | 2 | Wolverine | 1 | | 3 | Professor X | 1 | | 4 | Cyclops | 1 | | 4 | Shrek | 0 | | 4 | Superman | 1 | | 4 | Bob | 0 |

So you can pretend that Spiderman made The Hulk's and Wolverine's accounts. This tells me that Spiderman has an admin account because he's the first instance of the account_id "2".

So as you can see, while full_name is unique, there are many duplicate account_id's, I would like to refine it so that that it only displays the <strong>first instance</strong> of each ID - there are only 4 different account_id's so it should only show 4 entries, like so:

| account_id | full_name | Superhero? | | 1 | Batman | 1 | | 2 | Spiderman | 1 | | 3 | Professor X | 1 | | 4 | Cyclops | 1 |

How can I achieve this?

Answer1:

You should probably add another column. Now it is possible to get distinct record for every account_id using GROUP BY clause but results of all nonagreggated columns can be ambigius. You have to have some order you approve or indicator inside group to determine which record for every accout_id is "first". With column marking which record in each group is first query is simple. Without it you have to accept some order telling query which record is "first". On example alphabetical order of full_name:

SELECT account_id, full_name, email, login, phone, updated_at, last_request_at, unconfirmed_email FROM table1 WHERE full_name IN ( SELECT MIN(full_name) FROM table1 GROUP BY account_id WHERE id < '300' )

Answer2:

Here is the solution to what you asked for,

SELECT account_id,full_name,Superhero FROM( SELECT p.*,( CASE account_id WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow := 1 AND @curType := account_id END ) + 1 AS rank FROM test p, (SELECT @curRow := 0, @curType := '', @curRank := 0) r) A WHERE rank=2;

<a href="http://sqlfiddle.com/#!2/0b9ab/1" rel="nofollow"><strong>Sql_Demo</strong></a>

Output:

ACCOUNT_ID FULL_NAME SUPERHERO 1 Batman 1 2 Spiderman 1 3 Professor X 1 4 Cyclops 1

Answer3:

try this

select * from table1 group by account_id

<a href="http://sqlfiddle.com/#!2/9bfc1/3" rel="nofollow">demo here</a>

this will give you the first of every account_id

Recommend

  • select large data-size attribute from autocomplete search
  • Can I accidentally harm system files or personal files with Visual Studio 2012
  • How to Pass Image from Recyclerview to next Activity
  • Skin probability of a given pixel of a image
  • How to set focus on specific input?
  • How do you call function after client finishes download from tornado web server?
  • mysql group by php concat
  • Problem with Android Studio 3.2.1, corrupted code
  • Using PHP write an anagram function?
  • javaws : Java 7 update 45. Cannot send properties [duplicate]
  • How to Auto send SMS via Broadband USB dongle?
  • Postgresql & psycopg2: database does not exist
  • How to implement interleaved page allocation in a user-mode NUMA-aware memory allocator?
  • Windows Azure VM availability/Failover steps
  • How to import data to a specified tablespace
  • django - can't see the uploaded media files from apache
  • Create ranking for vector of double
  • Is there a way to use Firebase Legacy console from new account
  • Syncronizing database from Active Directory
  • Playing a monetized YouTube song inside of a Google Chrome Extension. Do I have any options?
  • Finding the number of elements less than or equal to k in a multiset
  • EIPHTTPProtocolExceptionPeer exception using PutBlock with array of bytes all set to zero
  • LINQ to Entities does not recognize the method 'System.String ToString()' method, and this
  • SQL append distinct values from two columns and two tables
  • phpmailer - How to verify a sent email arrived at its destination
  • How to extract a number from a string [duplicate]
  • Connect Node.js with Oracle on Windows platform
  • Change navbar in bootstrap if user login
  • Displaying a list of videos from a channel - Vimeo Advanced API
  • Meteor.. accounts- password— Create account on client without login
  • Generating anchors with PyYAML.dump()?
  • Configure Spring's MappingJacksonHttpMessageConverter
  • Error in installing package: fatal error: stdlib.h: no such file or directory
  • How to autopopulate a field in SugarCRM form
  • Is there a way to save the selected text and highlight it again once the page is refreshed?
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • How would I use PHP exceptions to define a redirect?
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • Busy indicator not showing up in wpf window [duplicate]
  • Why do underscore prefixed variables exist?