www.webdeveloper.com
Results 1 to 13 of 13

Thread: Need to choose one of several records

  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Need to choose one of several records

    I have a table that has from one to four records for each item, each with a different attribute for that item. It's actually a fairly complicated database with chemistry records, but I've created a simpler scenario in order to ask for advice. Here it is:

    Boxes are shipped to one of several warehouses. The boxes do not have unique identifiers, but can be uniquely identified using the following fields: FromState, FromCity, DateShipped, DateReceived, ReceivingStation, ReceivedBy.

    Each box can have from one to four attributes: Value, Girth, Weight, and HazMatCode.

    I need a query that will return only one record for each box, but that will choose the attribute by priority. So, if there is only one record for a box, that record is returned. If a box has more then one record, then I want to return the record for Value first; if there is no record for Value, then I want to return the record for Weight; if there is no record for Weight, then for Girth; and if no other records exist for the box, then return the record for HazMatValue. The result should be one record for each box that contains the highest-priority attribute.

    I've put together a table with some records that will hopefully illustrate what I'm trying for. As always, any help is appreciated.

    Code:
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `box_orders`
    -- ----------------------------
    DROP TABLE IF EXISTS `box_orders`;
    CREATE TABLE `box_orders` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `FromState` varchar(255) DEFAULT NULL,
      `FromCity` varchar(255) DEFAULT NULL,
      `DateShipped` varchar(255) DEFAULT NULL,
      `DateReceived` varchar(255) DEFAULT NULL,
      `ReceivingStation` varchar(255) DEFAULT NULL,
      `ReceivedBy` varchar(255) DEFAULT NULL,
      `BoxParameter` varchar(255) DEFAULT NULL,
      `Value` varchar(255) DEFAULT NULL,
      `Units` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of box_orders
    -- ----------------------------
    INSERT INTO `box_orders` VALUES ('2', 'OH', 'Akron', '9/19/2011', '9/16/2011', 'Glendale', 'Smith', 'Value', '150', 'Dollars');
    INSERT INTO `box_orders` VALUES ('3', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Value', '330', 'Dollars');
    INSERT INTO `box_orders` VALUES ('4', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Girth', '14', 'In');
    INSERT INTO `box_orders` VALUES ('5', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Weight', '6', 'Lbs');
    INSERT INTO `box_orders` VALUES ('6', 'NV', 'Pahrump', '12/1/2011', '12/4/2011', 'Glendale', 'Peterson', 'Weight', '18', 'Lbs');
    INSERT INTO `box_orders` VALUES ('7', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'Weight', '14', 'Lbs');
    INSERT INTO `box_orders` VALUES ('8', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'Girth', '22', 'In');
    INSERT INTO `box_orders` VALUES ('9', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'HazMatCode', '1', 'HazMatUnits');
    INSERT INTO `box_orders` VALUES ('10', 'TX', 'Taos', '11/19/2011', '11/22/2011', 'El Cajone', 'Burton', 'Value', '1100', 'Dollars');
    INSERT INTO `box_orders` VALUES ('11', 'TX', 'Taos', '11/19/2011', '11/22/2011', 'El Cajone', 'Burton', 'Weight', '21', 'Lbs');
    INSERT INTO `box_orders` VALUES ('12', 'AZ', 'Flagstaff', '12/1/2011', '12/6/2011', 'El Cajone', 'Burton', 'HazMatCode', '2', 'HazMatUnits');
    INSERT INTO `box_orders` VALUES ('13', 'OR', 'Portland', '10/8/2011', '10/17/2011', 'Glendale', 'Franklin', 'Value', '950', 'Dollars');
    INSERT INTO `box_orders` VALUES ('14', 'OR', 'Portland', '10/8/2011', '10/17/2011', 'Glendale', 'Franklin', 'Girth', '33', 'In');
    INSERT INTO `box_orders` VALUES ('15', 'WA', 'Seatlle', '12/8/2011', '12/15/2011', 'Carlsbad', 'Jackson', 'Weight', '15', 'Lbs');
    INSERT INTO `box_orders` VALUES ('16', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Value', '155', 'Dollars');
    INSERT INTO `box_orders` VALUES ('17', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Weight', '22', 'Lbs');
    INSERT INTO `box_orders` VALUES ('18', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Girth', '24', 'In');
    INSERT INTO `box_orders` VALUES ('19', 'NV', 'Reno', '9/9/2011', '9/12/2011', 'San Diego', 'Harris', 'Value', '160', 'Dollars');
    INSERT INTO `box_orders` VALUES ('20', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Value', '330', 'Dollars');
    INSERT INTO `box_orders` VALUES ('21', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Girth', '38', 'In');
    INSERT INTO `box_orders` VALUES ('22', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Weight', '22', 'Lbs');
    INSERT INTO `box_orders` VALUES ('23', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'HazMatCode', '3', 'HazMatUnits');

  2. #2
    Join Date
    Nov 2002
    Posts
    2,632
    Quote Originally Posted by Arch_Stanton View Post
    Boxes are shipped to one of several warehouses. The boxes do not have unique identifiers, but can be uniquely identified using the following fields: FromState, FromCity, DateShipped, DateReceived, ReceivingStation, ReceivedBy.
    I think there is some confusion on my part based on your wording. Looking at the table, those fields would not return a unique box. You could have multiple boxes coming from the same state and city, shipped and received at the same time, at the same station, and by the same person. They only unique identifier is your primary key, id. I would use a system where you have a package/box id that's printed on the label for the box and can be read and entered into the system for it to be tracked.

    Quote Originally Posted by Arch_Stanton View Post
    I need a query that will return only one record for each box, but that will choose the attribute by priority. So, if there is only one record for a box, that record is returned. If a box has more then one record, then I want to return the record for Value first; if there is no record for Value, then I want to return the record for Weight; if there is no record for Weight, then for Girth; and if no other records exist for the box, then return the record for HazMatValue. The result should be one record for each box that contains the highest-priority attribute.
    Again, the priority attribute was tripping me up since there is no priority field. I think what is going to make using this table difficult is the fact you use generic fields, BoxParameter, Value, and Units when you could just add the specific fields, weight, size, value, etc to the table. Maybe I'm showing my ignorance, but you might need to do multiple queries for each of the BoxParameters if need be. Do one select where you are looking for the box with a value. If nothing is returned, then do a query for weight, etc. Again, if you are only looking for a specific box, you already have the unique identifier in id.

  3. #3
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    792
    Changing your BoxParameter field to an Enum would make it possible to sort that column the way you need it prioritised:

    Code:
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for `box_orders`
    -- ----------------------------
    DROP TABLE IF EXISTS `box_orders`;
    CREATE TABLE `box_orders` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `FromState` varchar(255) DEFAULT NULL,
      `FromCity` varchar(255) DEFAULT NULL,
      `DateShipped` varchar(255) DEFAULT NULL,
      `DateReceived` varchar(255) DEFAULT NULL,
      `ReceivingStation` varchar(255) DEFAULT NULL,
      `ReceivedBy` varchar(255) DEFAULT NULL,
      `BoxParameter` enum('Value','Girth','Weight','HazMatCode') DEFAULT NULL,
      `Value` varchar(255) DEFAULT NULL,
      `Units` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of box_orders
    -- ----------------------------
    INSERT INTO `box_orders` VALUES ('2', 'OH', 'Akron', '9/19/2011', '9/16/2011', 'Glendale', 'Smith', 'Value', '150', 'Dollars');
    INSERT INTO `box_orders` VALUES ('3', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Value', '330', 'Dollars');
    INSERT INTO `box_orders` VALUES ('4', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Girth', '14', 'In');
    INSERT INTO `box_orders` VALUES ('5', 'CA', 'Chico', '10/1/2011', '10/3/2011', 'Carlsbad', 'Jackson', 'Weight', '6', 'Lbs');
    INSERT INTO `box_orders` VALUES ('6', 'NV', 'Pahrump', '12/1/2011', '12/4/2011', 'Glendale', 'Peterson', 'Weight', '18', 'Lbs');
    INSERT INTO `box_orders` VALUES ('7', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'Weight', '14', 'Lbs');
    INSERT INTO `box_orders` VALUES ('8', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'Girth', '22', 'In');
    INSERT INTO `box_orders` VALUES ('9', 'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving', 'HazMatCode', '1', 'HazMatUnits');
    INSERT INTO `box_orders` VALUES ('10', 'TX', 'Taos', '11/19/2011', '11/22/2011', 'El Cajone', 'Burton', 'Value', '1100', 'Dollars');
    INSERT INTO `box_orders` VALUES ('11', 'TX', 'Taos', '11/19/2011', '11/22/2011', 'El Cajone', 'Burton', 'Weight', '21', 'Lbs');
    INSERT INTO `box_orders` VALUES ('12', 'AZ', 'Flagstaff', '12/1/2011', '12/6/2011', 'El Cajone', 'Burton', 'HazMatCode', '2', 'HazMatUnits');
    INSERT INTO `box_orders` VALUES ('13', 'OR', 'Portland', '10/8/2011', '10/17/2011', 'Glendale', 'Franklin', 'Value', '950', 'Dollars');
    INSERT INTO `box_orders` VALUES ('14', 'OR', 'Portland', '10/8/2011', '10/17/2011', 'Glendale', 'Franklin', 'Girth', '33', 'In');
    INSERT INTO `box_orders` VALUES ('15', 'WA', 'Seatlle', '12/8/2011', '12/15/2011', 'Carlsbad', 'Jackson', 'Weight', '15', 'Lbs');
    INSERT INTO `box_orders` VALUES ('16', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Value', '155', 'Dollars');
    INSERT INTO `box_orders` VALUES ('17', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Weight', '22', 'Lbs');
    INSERT INTO `box_orders` VALUES ('18', 'WA', 'Seatlle', '12/4/2011', '12/10/2011', 'El Cajone', 'Burton', 'Girth', '24', 'In');
    INSERT INTO `box_orders` VALUES ('19', 'NV', 'Reno', '9/9/2011', '9/12/2011', 'San Diego', 'Harris', 'Value', '160', 'Dollars');
    INSERT INTO `box_orders` VALUES ('20', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Value', '330', 'Dollars');
    INSERT INTO `box_orders` VALUES ('21', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Girth', '38', 'In');
    INSERT INTO `box_orders` VALUES ('22', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'Weight', '22', 'Lbs');
    INSERT INTO `box_orders` VALUES ('23', 'OH', 'Akron', '10/3/2011', '10/10/2011', 'Glendale', 'Peterson', 'HazMatCode', '3', 'HazMatUnits');
    ... then testing it with a statement like so ...

    Code:
    SELECT t.*
    FROM (SELECT b.* FROM box_orders b ORDER BY b.BoxParameter) t
    GROUP BY t.FromState, t.FromCity, t.DateShipped, t.DateReceived, t.ReceivingStation, t.ReceivedBy
    You can tell me if it's correct or not
    Last edited by bionoid; 01-07-2012 at 08:58 AM.
    JavaScript: Learn | Validate | Compact | bionoid

  4. #4
    Join Date
    Nov 2002
    Posts
    2,632
    Quote Originally Posted by bionoid View Post
    Changing your BoxParameter field to an Enum would make it possible to sort that column the way you need it prioritised
    You can sort by ascending or descending. The problem is the order they want it checked follows neither, V, W, G, H. I do agree with the enum part though. The table in question was poorly designed, IMO.

  5. #5
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    792
    Quote Originally Posted by spufi View Post
    You can sort by ascending or descending. The problem is the order they want it checked follows neither, V, W, G, H. I do agree with the enum part though. The table in question was poorly designed, IMO.
    Enums do not sort by their alphabetic names, they are sorted by their internal indexes, so in the order I wrote them (note that I did not write them in alphabetical order):

    1 = Value
    2 = Girth
    3 = Weight
    4 = HazMatCode

    And they will be sorted accordingly, magic and if you don't believe me, try it.

    http://www.freeopenbook.com/mysqlcoo...6-sect-19.html
    Last edited by bionoid; 01-07-2012 at 03:26 PM. Reason: Enum indexes start at 1
    JavaScript: Learn | Validate | Compact | bionoid

  6. #6
    Join Date
    Jul 2010
    Posts
    8

    Choosing records with SQL

    Thanks for your reply, spufi. The table I provided is not an actual table I"m using, just a quick-and-dirty illustration of the point I was trying to make. In my example, I want to assume that the fields FromState through ReceivedBy uniquely identify a box (sorry if I didn't make that clear).

    My challenge is that I have from one to four records coming in for each box, each containing one attribute. I have no control over how many records come in, and I cannot make the client add a new field for each attribute. What I need to do is return a table with one record per box, based on the priority I specified. If they were all "Value", that would be great, but sometimes one will come in with no value but only a Weight and HazMatCode, or something like that. I can do this in PHP, but before I do, I'd like to see if there's some more elegant solution using SQL.

    Thanks again for your replies.

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    Bionoid, thanks so much for your reply. The thing is, I'm not trying to sort, I'm trying to return only one record for each box, based on the priority I listed. If there's a record for value, then that's the one returned. If not, then the one for weight is returned, and so on.

    Maybe I need to do this in PHP, but it always amazes me how elegant SQL can do things, so I thought I'd ask. Thanks again, and sorry to be so vague in my original post.

  8. #8
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    792
    Quote Originally Posted by Arch_Stanton View Post
    Bionoid, thanks so much for your reply. The thing is, I'm not trying to sort, I'm trying to return only one record for each box, based on the priority I listed. If there's a record for value, then that's the one returned. If not, then the one for weight is returned, and so on.

    Maybe I need to do this in PHP, but it always amazes me how elegant SQL can do things, so I thought I'd ask. Thanks again, and sorry to be so vague in my original post.
    You do need to sort. You want your items in the right order and then take the first one using a group by.

    The example I provided was tested on my machine and gave me the correct unique enties by BoxParameter (obviously with the Enum in place).
    The query was obviously very basic, because you could do all your filters in the inner select before grouping the final result.

    The data returned to me was this... check and see if it is right to you (I didn't order the final result, but I can if you want me to):

    Code:
    12, 'AZ', 'Flagstaff', '12/1/2011',  '12/6/2011',  'El Cajone', 'Burton',   'HazMatCode', '2',    'HazMatUnits'
    3,  'CA', 'Chico',     '10/1/2011',  '10/3/2011',  'Carlsbad',  'Jackson',  'Value',      '330',  'Dollars'
    8,  'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving',   'Girth',      '22',   'In'
    6,  'NV', 'Pahrump',   '12/1/2011',  '12/4/2011',  'Glendale',  'Peterson', 'Weight',     '18',   'Lbs'
    19, 'NV', 'Reno',      '9/9/2011',   '9/12/2011',  'San Diego', 'Harris',   'Value',      '160',  'Dollars'
    20, 'OH', 'Akron',     '10/3/2011',  '10/10/2011', 'Glendale',  'Peterson', 'Value',      '330',  'Dollars'
    2,  'OH', 'Akron',     '9/19/2011',  '9/16/2011',  'Glendale',  'Smith',    'Value',      '150',  'Dollars'
    13, 'OR', 'Portland',  '10/8/2011',  '10/17/2011', 'Glendale',  'Franklin', 'Value',      '950',  'Dollars'
    10, 'TX', 'Taos',      '11/19/2011', '11/22/2011', 'El Cajone', 'Burton',   'Value',      '1100', 'Dollars'
    16, 'WA', 'Seatlle',   '12/4/2011',  '12/10/2011', 'El Cajone', 'Burton',   'Value',      '155',  'Dollars'
    15, 'WA', 'Seatlle',   '12/8/2011',  '12/15/2011', 'Carlsbad',  'Jackson',  'Weight',     '15',   'Lbs'
    Anyways, It's your choice.

    Good luck.
    Last edited by bionoid; 01-07-2012 at 04:35 PM. Reason: Formatted results
    JavaScript: Learn | Validate | Compact | bionoid

  9. #9
    Join Date
    Jul 2010
    Posts
    8
    Hey Bionoid, I just looked more closely at your solution, and I think you've put me on the right path (sorry, I posted my last reply before I'd played with your code). That returns one record per box, all right, just the way I wanted (subquery! duh!).

    But I'm still fuzzy on how to pick which record is returned. I change the parameter field to enum and filled in the values, but no soap. If your code could return the record based on the parameter priority, I'd be in Fat City.

    Thanks again for your help.

  10. #10
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    792
    Because I do an ORDER BY in the subquery, the most relevant item parameter is on top, so when the GROUP BY happens it is the only one taken (per item).

    To sort the final result by BoxParameter:

    Code:
    SELECT t.*
    FROM (SELECT b.* FROM box_orders b ORDER BY b.BoxParameter) t
    GROUP BY t.FromState, t.FromCity, t.DateShipped, t.DateReceived, t.ReceivingStation, t.ReceivedBy
    ORDER BY t.BoxParameter
    The result, notice the Enum managing the order (the same applied for the subquery):

    Code:
    2,  'OH', 'Akron',     '9/19/2011',  '9/16/2011',  'Glendale',  'Smith',    'Value',      '150',  'Dollars'
    3,  'CA', 'Chico',     '10/1/2011',  '10/3/2011',  'Carlsbad',  'Jackson',  'Value',      '330',  'Dollars'
    10, 'TX', 'Taos',      '11/19/2011', '11/22/2011', 'El Cajone', 'Burton',   'Value',      '1100', 'Dollars'
    13, 'OR', 'Portland',  '10/8/2011',  '10/17/2011', 'Glendale',  'Franklin', 'Value',      '950',  'Dollars'
    16, 'WA', 'Seatlle',   '12/4/2011',  '12/10/2011', 'El Cajone', 'Burton',   'Value',      '155',  'Dollars'
    19, 'NV', 'Reno',      '9/9/2011',   '9/12/2011',  'San Diego', 'Harris',   'Value',      '160',  'Dollars'
    20, 'OH', 'Akron',     '10/3/2011',  '10/10/2011', 'Glendale',  'Peterson', 'Value',      '330',  'Dollars'
    8,  'NV', 'Las Vegas', '11/14/2011', '11/18/2011', 'San Diego', 'Irving',   'Girth',      '22',   'In'
    6,  'NV', 'Pahrump',   '12/1/2011',  '12/4/2011',  'Glendale',  'Peterson', 'Weight',     '18',   'Lbs'
    15, 'WA', 'Seatlle',   '12/8/2011',  '12/15/2011', 'Carlsbad',  'Jackson',  'Weight',     '15',   'Lbs'
    12, 'AZ', 'Flagstaff', '12/1/2011',  '12/6/2011',  'El Cajone', 'Burton',   'HazMatCode', '2',    'HazMatUnits'
    If you're looking for a single entry then you could do it like so:

    Code:
    SELECT t.*
    FROM (
      SELECT b.*
      FROM box_orders b
      WHERE
        b.FromState        = "OH" AND
        b.FromCity         = "Akron" AND
        b.DateShipped      = "10/3/2011" AND
        b.DateReceived     = "10/10/2011" AND
        b.ReceivingStation = "GlenDale" AND
        b.ReceivedBy       = "Peterson"
      ORDER BY b.BoxParameter
    ) t
    GROUP BY t.FromState, t.FromCity, t.DateShipped, t.DateReceived, t.ReceivingStation, t.ReceivedBy
    LIMIT 1
    ( If there is a better way I'm all ears )
    Last edited by bionoid; 01-07-2012 at 05:28 PM.
    JavaScript: Learn | Validate | Compact | bionoid

  11. #11
    Join Date
    Jul 2010
    Posts
    8
    Bionoid, I had to fool around with it a bit in order to figure out why your solution worked, and why the enum data type was necessary, but now I see it. Not only did you solve my problem, but you've added a cool coding technique to my bag of tricks. Thanks again for taking the time to help me.

    Grateful noob. :|

  12. #12
    Join Date
    Dec 2011
    Location
    Centurion, South Africa
    Posts
    792
    You're welcome.

    Good luck.
    JavaScript: Learn | Validate | Compact | bionoid

  13. #13
    Join Date
    Nov 2002
    Posts
    2,632
    Quote Originally Posted by bionoid View Post
    Enums do not sort by their alphabetic names, they are sorted by their internal indexes, so in the order I wrote them (note that I did not write them in alphabetical order):

    1 = Value
    2 = Girth
    3 = Weight
    4 = HazMatCode

    And they will be sorted accordingly, magic and if you don't believe me, try it.

    http://www.freeopenbook.com/mysqlcoo...6-sect-19.html
    I'll add this to the list of things I knew but have forgotten. I have that MySQL Cookbook. I'm just getting back into development so somethings are still a little foggy.

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles