mysql - Select only unique rows with INNER JOIN query -


this question has answer here:

sorry, not quite sure how word this.

there 2 database tables, store_locations , store_diagnostics.

store_locations holds static information store, such zip_code, storeid , few other things. while store_diagnostics holds diagnostic data , stats on each store location, such total_sold, total_revenue, etc.

new entries entered store_diagnostics every hour or via cron job checks total sales, etc. issue on behalf instead of updating existing rows, new row created each time cron job runs sales week can compared last week etc.

my current query works, fetches multiple entries same store. ideally, i'd fetch latest store_diagnostics row given storeid , order them created field descending last created row particular store.

here existing query:

select   locations.* ,   location_stats.total_sold ,   location_stats.total_moved_to ,   location_stats.total_moved_from ,   location_stats.total_revenue ,   location_stats.created store_locations locations   inner join     store_diagnostics location_stats   on     locations.storeid = location_stats.storeid order location_stats.created desc , location_stats.total_sold desc

as can see, i'd fetch 1 result per storeid , i'd fetched after ordering rows created column in desc order.

the location_stats.total_sold sorting method i'd used on results, unique stores sorted total amount of items sold.

i sorry if unclear, hope have explained sufficiently.

i added clause filters based on maximum created top value if sorted in descending order. overall sort order changed total_sold indicated in problem description. note if cron job generates new rows faster precision of created, you'll duplicates. i'm assuming doesn't. if have identity pk in store_diagnostics, i'd take max of rather created.

option 1: use correlated subquery

select   locations.* ,   location_stats.total_sold ,   location_stats.total_moved_to ,   location_stats.total_moved_from ,   location_stats.total_revenue ,   location_stats.created store_locations locations   inner join store_diagnostics location_stats on locations.storeid = location_stats.storeid location_stats.created = (select max(created) store_diagnostics stored_id = locations.stored_id) order location_stats.total_sold desc

option 2: join groupby

select   locations.* ,   location_stats.total_sold ,   location_stats.total_moved_to ,   location_stats.total_moved_from ,   location_stats.total_revenue ,   location_stats.created store_locations locations   inner join store_diagnostics location_stats on locations.storeid = location_stats.storeid   inner join (select store_id, max(created) created stored_diagnostics group store_id) mx on locations.store_id = mx.stored_id , location_stats.created = mx.created order location_stats.total_sold desc

Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more