sql - Get top 1 row of each group -


i have table want latest entry each group. here's table:

documentstatuslogs table

|id| documentid | status | datecreated | | 2| 1          | s1     | 7/29/2011   | | 3| 1          | s2     | 7/30/2011   | | 6| 1          | s1     | 8/02/2011   | | 1| 2          | s1     | 7/28/2011   | | 4| 2          | s2     | 7/30/2011   | | 5| 2          | s3     | 8/01/2011   | | 6| 3          | s1     | 8/02/2011   |

the table grouped documentid , sorted datecreated in descending order. each documentid, want latest status.

my preferred output:

| documentid | status | datecreated | | 1          | s1     | 8/02/2011   | | 2          | s3     | 8/01/2011   | | 3          | s1     | 8/02/2011   |

  • is there aggregate function top each group? see pseudo-code getonlythetop below:

    select documentid, getonlythetop(status), getonlythetop(datecreated) documentstatuslogs group documentid order datecreated desc

  • if such function doesn't exist, there way can achieve output want?

  • or @ first place, caused unnormalized database? i'm thinking, since i'm looking 1 row, should status located in parent table?

please see parent table more information:

current documents table

| documentid | title  | content  | datecreated | | 1          | titlea | ...      | ...         | | 2          | titleb | ...      | ...         | | 3          | titlec | ...      | ...         |

should parent table can access status?

| documentid | title  | content  | datecreated | currentstatus | | 1          | titlea | ...      | ...         | s1            | | 2          | titleb | ...      | ...         | s3            | | 3          | titlec | ...      | ...         | s1            |

update learned how use "apply" makes easier address such problems.

;with cte (    select *,          row_number() on (partition documentid order datecreated desc) rn    documentstatuslogs ) select * cte rn = 1

if expect 2 entries per day, arbitrarily pick one. both entries day, use dense_rank instead

as normalised or not, depends if want to:

  • maintain status in 2 places
  • preserve status history
  • ...

as stands, preserve status history. if want latest status in parent table (which denormalisation) you'd need trigger maintain "status" in parent. or drop status history table.


Comments

Post a Comment

Popular posts from this blog

Change php variable from jquery value using ajax (same page) -

what i'm trying this: i've php global variable $currentid , simple jquery script check if there "id" (page) in hash of url , if yes wanted give value $currentid . i've been reading lot , notice shall use ajax i'm not managing it. my code is: <script type="text/javascript"> $(document).ready(function(){ if (parent.location.hash != '' ) { var thecurrentid = parent.location.hash.split('#')[1]; $.ajax({ url: 'my.php', //current page type: 'post', data: {theid: thecurrentid }, datatype: 'json', success: function () { } }); } // code here }); </script> could me please? thank in advance. php server side language, meaning once has sent data browser, php variables , data cannot changed... however, using ajax, can send request php page , change data in dom based on result. consider th...
Read more

Pull out data related to my apps from Android Play Store and iOS App Store -

i want pull out data related apps (ratings, reviews, etc) google play store , apple app store ? how can accomplish ? i have found open source apis google mentioned not reliable. also there paid apis (e.g. appfigures) available. has idea how able pull data these play stores ?? i looking direction on how can accomplish this. can suggest ? we (appfigures) have been working on developing infrastructure give ability reviews , bunch of other data points major app stores several years :] we make available via our api though may want check out.
Read more

How can I fetch data from a web server in an android application? -

i want retrieve data web server in android application, , don't know begin. should use web services? i recommend these tutorials: connect android php , mysql , json in android , php , mysqli i used these tutorials , managed trying working without difficulty. between them describe each step in how attempting @ each stage, android application, database , web server side , has information included can process , use received information the thing add connect android php , mysql tutorial makes use of mysql_ in php deprecated. better use mysqli why included third link. the basic outline of want this: 1) in android app make request server php script using class this: import java.io.bufferedreader; import java.io.ioexception; import java.io.inputstream; import java.io.inputstreamreader; import java.io.unsupportedencodingexception; import java.util.list; import org.apache.http.httpentity; import org.apache.http.httpresponse; import org.apache.http.namevaluepair...
Read more