1962

How to extract two rows for each unique user id

Following query used to extract the following column & rows.

issue is i am not able extract two rows from each user ID . If i am using limit then fetches only 1st two rows.

so how i can extract the two rows for each Unique user id.

For example :

two rows of userid 222 two rows of userid 122 two rows of userid 367

and so on

TEXT OP :

Expected output :

Answer1:

Assuming <strong>id</strong> is your user id and <strong>post_ID</strong> is unique.

(SELECT id,name,post,Image,post FROM users GROUP BY id ORDER BY id ) UNION (SELECT id,name,post,Image,post FROM users WHERE post_ID NOT IN (SELECT post_ID FROM users GROUP BY id ORDER BY id) GROUP BY id ORDER BY id ) ORDER BY id, post_ID <hr>

Please check my query from which I get the below answer:

( SELECT store_id, `key_id` , `string` , `translate` , `locale` FROM `core_translate` AS C GROUP BY `store_id` ORDER BY `store_id` ) UNION ( SELECT store_id, `key_id` , `string` , `translate` , `locale` FROM `core_translate` AS C WHERE `key_id` NOT IN ( SELECT `key_id` FROM `core_translate` AS C GROUP BY `store_id` ORDER BY `store_id` ) GROUP BY `store_id` ORDER BY C.`store_id` ) ORDER BY store_id, `key_id` <hr> store_id key_id string translate locale 0 20 Screensize Screensize en_US 0 21 Gender Gender en_US 1 1 Size Size en_US 1 2 Color Color en_US 2 5 Large Image Large Image en_US 2 6 Medium Image Medium Image en_US 3 10 Manufacturer Manufacturer en_US 3 11 Dimensions Dimensions en_US 4 15 Description Description en_US 4 16 Weight Weight en_US

Answer2:

Not sure this is workable on mysql, but you can this. I'm assuming id is user id and post_ID is unique.

<strong>EDIT</strong>

try to workaround mysql limitation.

select id,name,post_Imagename_small,Image_title,post_ID from users u1 where u1.post_ID in ( select u3.post_ID from ( select u2.post_ID from users u2 where u2.id = u1.id limit 2 ) s u3 ) order by u1.id

<strong>EDIT 2</strong>

in the case that the answer doesn't work, here is a good reading on this sort of querying problem, there're various solving approachs too.

Recommend

  • Linq Full Outer Join on Two Objects
  • SQL append distinct values from two columns and two tables
  • Hibernate: Inheritance and relationship mapping + generics
  • Should I use composite primary keys in Grails?
  • distinct values from multiple fields within one table ORACLE SQL
  • Display validation errors inside
  • Image map in Flex
  • pillow imaging ImportError
  • How can I display the parent menu item's description using Wordpress walkers?
  • Android application: how to use the camera and grab the image bytes?
  • How to view images from protected folder with php?
  • How does document.ready work with angular element directives?
  • Using a canvas object in a thread to do simple animations - Java
  • How can I enlarge video fullscreen without the affected interface project in as3?
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Python urlparse: small issue
  • Breeze - Deleted Items nav properties bug
  • How to avoid particles glitching together in an elastic particle collision simulator?
  • Scrapy recursive link crawler
  • FileReader+canvas image loading problem
  • Recording logins for password protected directories
  • Splitting given String into two variables - php
  • Read text file and split every line in MSBuild
  • javaw.exe and eclipse startup problems
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Check if a string to interpolate provides expected placeholders
  • Cassandra Data Model
  • How to get next/previous record number?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How do you join a server to an Active Directory (domain)?
  • Memory offsets in inline assembly
  • embed rChart in Markdown
  • need help with bizarre java.net.HttpURLConnection behavior
  • Turn off referential integrity in Derby? is it possible?
  • How does Linux kernel interrupt the application?
  • python draw pie shapes with colour filled
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize