61279

Select top 1 for distinct value according to time

Question:

I got following tables :

<strong>tblMyFriends</strong>

ID FriendID GroupID Public 1 F1 YES 2 F2 YES 3 F3 NO 3 G1 YES 4 G2 YES 6 F4 NO 7 F5 YES 8 G3 NO

<strong>tblMessages</strong>

ID FriendID GroupID MyMessage MyTime 1 F1 A1 2 2 F4 A2 3 3 F1 A3 1 3 G2 Y1 1 4 G2 Y2 3 6 F3 A4 3 7 F3 A5 4 8 G3 Y3 5 9 F4 A6 5 10 F4 A7 6

I need to fetch the latest message(only Top 1) i.e. decreasing order of time. Also, followed by the remaining list of friend or group in my list

Hence, i will need query which will return following output:

<strong>tblOutput</strong>

ID FriendID GroupID MyMessage MyTime 1 F4 A7 6 2 G3 Y3 5 3 F3 A5 4 4 G2 Y2 3 5 F1 A1 2 6 F2 0 7 F5 0 8 G2 0

i'm using SQLite

Answer1:

To get records for friends/groups that do not have messages, use an <a href="http://en.wikipedia.org/wiki/Join_%28SQL%29#Outer_join" rel="nofollow">outer join</a>.

To get a single key, which makes joining easier, use the <a href="http://www.sqlite.org/lang_corefunc.html#coalesce" rel="nofollow">coalesce function</a>. (This requires that the empty fields are NULL.)

To get one result record for each group of records use GROUP BY:

SELECT tblMyFriends.FriendID, tblMyFriends.GroupID, tblMessages.MyMessage, MAX(tblMessages.MyTime) FROM tblMyFriends LEFT JOIN tblMessages ON COALESCE(tblMyFriends.FriendID, tblMyFriends.GroupID) = COALESCE(tblMessages.FriendID, tblMessages.GroupID) GROUP BY tblMyFriends.FriendID, tblMyFriends.GroupID

This requires SQLite 3.7.11 or later; otherwise, the MyMessage values will not be from the same record that matches the MAX.

Recommend

  • spring integration time out clients
  • message box in asp.net web application
  • Android : Alarm Notification Issue
  • Image In Scroll View
  • Why is 0x20 subtracted from the stack pointer in the prologue of this function's code?
  • Java String.format adding spaces to integers
  • How to find F(x,0) when F(x,i) = F(x-1,i) xor F(x-1, i+1) xor … F(x-1,n) in less than linear time
  • Excel: how to create a number in a column according to a text value in other column?
  • Multiplying arrays with scalars and adding in VBA
  • Label Areas in Python Matplotlib stackplot
  • Excel sumif with criteria
  • Customize legend in ggplot
  • VBA Check Row For Value Then Another Value and Paste If True
  • Excel VBA - Using Find method on a range of dates
  • How to convert date to week number
  • Fill value backwards from occurence by group
  • php_network_getaddresses: getaddrinfo failed
  • How to get message from MessageIDTerm for Yahoo IMAP profile?
  • How to get rows with min values in one column, grouped by other column, while keeping other columns?
  • Retrieve List of Cells in Excel Where Column Equals Something
  • Conditionally ignore primitive typed fields with Jackson
  • Fortran: Array of arbitrary dimension?
  • Password strong - not require each condition
  • How to move object along the polygons
  • Java Circle-Circle Collision Detection
  • How to write .bin file in R and open it in Fortran
  • OpenXML how to get cell in range
  • How to run a query between dates and times?
  • What's the best way to create a menu in jQueryMobile
  • Tips for creating scalable WPF user control
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • Adding/Removing Lines from a JPanel
  • Creating a layer of gradient within an SVG path dynamically
  • Excel distinct count with conditions
  • How to concat Pandas dataframe columns
  • several dataProvider per one Test in TestNG
  • Using Sax parsing to edit and write XML in VB6
  • Excel's Macro-Recorder usage
  • VBA Convert delimiter text file to Excel
  • Getting Messege Twice Using IMvxMessenger