mysql - Getting minimum value for three columns group by a single column in a single table -

-

i have single table , table structure this:

unique_id     vendor_name      price1     price2     price3    code   1             vendor 1         0.0012     0.0014     0.0054     125 2             vendor 2         0.0015     0.0016     0.0050     125 3             vendor 3         0.0011     0.0019     0.0088     125 4             vendor 1         0.0025     0.0024     0.0034     126 5             vendor 2         0.0043     0.0019     0.0065     126 6             vendor 3         0.0019     0.0085     0.0082     126

i have fetch min price each price column group code. , expected output follows:

code          price1          price2      price3     vendor price1      vendor price 2      vendor price 3  125           0.0011          0.0014      0.0050     vendor3                vendor1                 vendor 2  126           0.0019          0.0019      0.0034     vendor3                vendor2                 vendor 1

so mysql query fetch records this? , have make query fetch maximum , second highest value table , there may number of rows single code.

my data in sql fiddle.

in second highest value case output should as:

code          price1          price2      price3     vendor price1      vendor price 2      vendor price 3 125           0.0012          0.0016      0.0054     vendor1                vendor2                 vendor 1  126           0.0025          0.0024      0.0065     vendor1                vendor1                 vendor 2 

select     data.*,     v1.vendor_name 'vendor price1',     v2.vendor_name 'vendor price2',     v3.vendor_name 'vendor price3'     (         select             code,             min(price1) price1,             min(price2) price2,             min(price3) price3                     tbl         group code     ) data     left join     (         select              min(vendor_name) vendor_name,             code                     tbl                     price1 =              (                 select                     min(price1)                                     tbl t                                     t.code = tbl.code             )         group code     ) v1 on data.code = v1.code     left join     (         select              min(vendor_name) vendor_name             code                     tbl                     price2 =              (                 select                     min(price2)                                     tbl t                                     t.code = tbl.code             )         group code     ) v2 on data.code = v2.code     left join     (         select              min(vendor_name) vendor_name             code                     tbl                     price3 =              (                 select                     min(price3)                                     tbl t                                     t.code = tbl.code             )         group code     ) v3 on data.code = v3.code

even though query looks pretty big, joins repeated 3 times.

update updated query removing limit , adding min(vendor_name) vendor_name instead of vendor_name.

demo

here

output

| code | price1 | price2 | price3 | vendor price1 | vendor price2 | vendor price3 | ----------------------------------------------------------------------------------------------- |  125 | 0.0011 | 0.0014 | 0.0050 |          vendor 3 |          vendor 1 |          vendor 2 | |  126 | 0.0019 | 0.0019 | 0.0034 |          vendor 3 |          vendor 2 |          vendor 1 |

Popular posts from this blog

How to calculate SNR of signals in MATLAB? -

-
i've encountered simple, yet fundamental problem on calculating snr: there several signals : s1 = original , pure clean signal without noise. n1 = white gaussian noise going added s1. s2 = s1 + n1 (the noisy signal before performing noise-reduction algorithm) s3 = noise-reduced signal (after performing noise reduction algorithm) n2 = s3 - s1 (the amount of noise after performing noise-reduction algorithm) now want compare snr before , after performing noise-reduction algorithm. which signals should consider snr_before!? s1/n1 or s2/n1 ? which signals should consider snr_after!? s3/n1 or s3/n2 ? which commands or functions should use in matlab in order compute snr_before , snr_after? thanks billion putting time on helping me. what know calculating snr before : snrbeforenoise = mean( signal .^ 2 ) / mean( noise .^ 2 ); and snr after : residual_noise = signal - noise_reduced_signal; snr_after = mean( signal .^ 2 ) / mean( residual_noise .^ 2...
Read more

c# - Attempting to upload to FTP: System.Net.WebException: System error -

-
i have api takes in xml , uploads files based on information in xml. uploads on schedule (also xml), , have tested surrounding , know works. i getting error 40% of time on first file attempt upload in each time cycle (time cycle = 45 minutes files, 30 minutes others). here code upload: try { loggerftp.log("uploading file: " + filename, false); // create request. ftpwebrequest request = (ftpwebrequest)webrequest.create(appsettingsftp.ftpurl + @"/" + filename); request.method = webrequestmethods.ftp.uploadfile; request.timeout = 6000000; //set 100 minutes //request.timeout = -1; //set infinite // add login credentials. request.credentials = new networkcredential(appsettingsftp.ftplogin, appsettingsftp.ftppassword); // grab file contents. streamreader sourcestream = new streamreader(appsettingsftp.uploadfiledirectory + filename); byte[] filecontents = encoding.utf8.getbytes(sourcestream.readtoend()); sourcest...
Read more

ios - UISlider customization: how to properly add shadow to custom knob image -

-
Image
i need place custom knob image shadow uislider. far have image of 38x38, of there region defines shadow of knob. now while using image, when slide knob extreme ends actual knob not able cover end points, hence end points visible under transparent region of knob image this code using customize uislider uiimage *slidebtimg = [uiimage imagenamed:sliderknobimg]; [self.ratingslider setthumbimage:slidebtimg forstate:uicontrolstatenormal]; [self.ratingslider setthumbimage:slidebtimg forstate:uicontrolstatehighlighted]; how can implement custom knob shadow beneath it, covering end points?? look inside uislider.h , there methods let customize slider knob , track needed. (ios 2.0 , above) // lets subclass lay out track , thumb needed - (cgrect)minimumvalueimagerectforbounds:(cgrect)bounds; - (cgrect)maximumvalueimagerectforbounds:(cgrect)bounds; - (cgrect)trackrectforbounds:(cgrect)bounds; - (cgrect)thumbrectforbounds:(cgrect)bounds trackrect:(cgrect)rect value:...
Read more