selecting/searching rows of a Table based on the value of a field in the table.

[ajoo]

Hi all ! I am very new to mysql and i am trying to learn. I need a little help. I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:

 

Day_ID -- Dues ---  Last_Visit ---  Points.

1  --------- 900 -------- 1/12  --------   6     

2  --------- 700 -------- 4/12  --------   7

3  --------- 600 -------- 7/12  --------   5

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

 

So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6. 

 

So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:

 

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

 

Can someone show me how this may be achieved? 

 

Thanks all in advance for sharing their knowledge and time.

 

P.S. I am not attaching a Table as its a very small example. I hope that's OK.

 

 

 

 

 

 

 

 

[Barand]

This is a problem that depends on dates of visits to find the last and consecutive visits. Unfortunately, those date formats are not suitable for date storage in a database. If the last visit in this case were 10/01 following the 14/12 visit there is no way of knowing that that should be the last when sorted by date. (Do not rely on id sequences, use date/times when sequence is an issue). Store dates in yyyy-mm-dd format which is sortable.

 

So the problem is to find the points in the latest and find the latest date before that where the point value is different. Then retrieve the records since that date.

<?php
$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$db->query("DROP TABLE IF EXISTS clubvisit");

$sql = "CREATE TABLE clubvisit (
    day_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    dues INT,
    last_visit DATE,
    points INT
    )";
$db->query($sql);

$sql = "INSERT INTO clubvisit VALUES
    (1  , 900 , '2012-12-01'  ,   6),
    (2  , 700 , '2012-12-04'  ,   7),
    (3  , 600 , '2012-12-07'  ,   5),
    (4  , 600 , '2012-12-09'  ,   6),
    (5  , 600 , '2012-12-10'  ,   6),
    (6  , 600 , '2012-12-14'  ,   6)";
$db->query($sql);

$sql = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >
    (
    SELECT MAX(last_visit) FROM clubvisit
    WHERE points <>
        (
        SELECT points as lastpoints
        FROM clubvisit
        JOIN
            (
            SELECT MAX(last_visit) as last_visit
            FROM clubvisit
            ) as latest USING (last_visit)
        )
    )";

?>

RESULTS:

+--------+------+------------+--------+
| day_id | dues | last_visit | points |
+--------+------+------------+--------+
|      4 |  600 | 2012-12-09 |      6 |
|      5 |  600 | 2012-12-10 |      6 |
|      6 |  600 | 2012-12-14 |      6 |
+--------+------+------------+--------+

[ajoo]

Hi Guru ! Surely you must be one ( a guru ) to think of a query like the one above. I'ld be grateful if you can please explain the query that you have devised and also your modus operandi to think so straight while creating a query like this ! I'ld sure love to emulate your thought process. Please do explain how the query works by breaking it up like you have done

.

Thanks and very grateful.  

Ajoo

[Barand]

As I said earlier

 

So the problem is to find the points in the latest and find the latest date before that where the point value is different. Then retrieve the records since that date.

 

 

so here is the anatomy of the query

SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >
    (
    SELECT MAX(last_visit) FROM clubvisit                                            --+
    WHERE points <>                                                                    | 
        (                                                                              | find the
        SELECT points as lastpoints                             --+                    | latest date
        FROM clubvisit                                            | find points        | that had a
        JOIN                                                      | value from         | point value
            (                                                     | the record that    | not equal to
            SELECT MAX(last_visit) as last_visit    --+ get       | matches the        | points value
            FROM clubvisit                            | latest    | latest date in     | found in the
            )                                       --+ date      | the subquery by    | latest record
            as latest USING (last_visit)                        --+ JOIN on the date --+
        )
    )

[ajoo]

Thanks Guru !!  I did manage to find the explanation using the myphpAdmin by taking parts as you have also segregated. So I ran the SQL's and got the results of the portions. I have a few questions on this but i'll first try and read a bit more and hammer them out myself and if I cannot, then i'll come and ask them again.

Thanks a lot.

[ajoo]

Hi Barand, The previous query that you were so kind to help me with, I have altered a little as follows :

$sql = "SELECT COUNT(last_visit) as numcount FROM clubvisit
         WHERE points <>
          (
           SELECT points as lastpoints
           FROM clubvisit
        JOIN
            (
            SELECT MAX(last_visit) as last_visit
            FROM clubvisit
            ) as latest USING (last_visit)
        );"

It works as desired in myphpAdmin.  However now i am using this as

$result = mysqli_query($link,$sql) 

 and I am not sure how I may retrieve the value of numcount from this one. kindly guide. Thanks !

Edited October 11, 2013 by ajoo

[Barand]

$result = mysqli_query($link,$sql) ;
$row = mysqli_fetch_assoc($result);
echo $row['numcount'];

That will give a count of the visits where the points not equal 6 (ie 2). Is that what you now want?

Edited October 11, 2013 by Barand

[ajoo]

Yup  great ! thanks . Thanks loads 

[ajoo]

Hi Barand,

 

I have achieved what i wanted with your help but I just want to confirm if there is a more elegant way to achieve it. So this query that you created initially to solve this issue returns the latest three rows with the points value 6. However if i did not wish to have have these rows listed and instead just wanted to know the count of rows that were returned, can the query be modified to achieve that? That's what I have been trying and thought I had managed but I had not. So i used the result in mysqli_affected_rows and indirectly counted the rows to be three. However I was wondering and trying to achieve the same with count in the query. I am sure it can be done and request you to show me how to do it.

 

Thanks again !

[Barand]

Simples!

$sql = "SELECT COUNT(*) as numcount
FROM clubvisit cv
WHERE last_visit >
    (
    SELECT MAX(last_visit) FROM clubvisit
    WHERE points <>
        (
        SELECT points as lastpoints
        FROM clubvisit
        JOIN
            (
            SELECT MAX(last_visit) as last_visit
            FROM clubvisit
            ) as latest USING (last_visit)
        )
    )";

[ajoo]

hey  Thanks  ! but guess what I tried and got it too. 

SELECT count(last_visit) 
FROM clubvisit cv
WHERE last_visit >
    (
    SELECT MAX(last_visit) FROM clubvisit
    WHERE points <>
        (
        SELECT points as lastpoints
        FROM clubvisit
        JOIN
            (
            SELECT MAX(last_visit) as last_visit
            FROM clubvisit
            ) as latest USING (last_visit)
        )
    );

So i was also trying it even after posting the query. Thanks loads cos I am learning too with your help. 

[ajoo]

Hi Barand ( guru),

 

I am here once again with another query that i wish to form from the same table clubvisits. The table of entries is as below.

 

Day_ID -- Dues ---  Last_Visit ---  Points.

1  --------- 900 -------- 1/12  --------   6     

2  --------- 700 -------- 4/12  --------   7

3  --------- 400 -------- 7/12  --------   4

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

7  --------- 500 -------- 10/12 -------   5

8  --------- 500 -------- 14/12 -------   5

 

The last time you created a query which checked for last entry value of Dues and the found all the entries for which the dues were same. This time I wish to change that slightly so that it finds the latest dues value (500) and checks for all records with the same dues value as well as all the value of the next dues values. i.e.  I want to make a query whose result would be 

 

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 14/12 -------   6

7  --------- 500 -------- 10/12 -------   5

8  --------- 500 -------- 14/12 -------   5.

 

I am able to get

 

6  --------- 600 -------- 14/12 -------   6

7  --------- 500 -------- 10/12 -------   5

8  --------- 500 -------- 14/12 -------   5.

 

if i change " Where last visit > " to " Where last visit >= " 

but I am not able to get the rest of the entries for which the dues value = 600. Help sought.

 

Thanks again !

Edited October 18, 2013 by ajoo

[Barand]

Before I attempt the query a few comments on the data

 

That is not the same data as before. Point values have changed and extra rows.

 

It is now a prime example of why you cannot rely on id for the sequence. ID 7 has an earlier date than ID 6.

 

That means you need sortable date formats and you are still using the original (useless) format.

 

You have multiple visits on the same date - which is the "last" on 14/12, the one with 5 points or the one with 6? You need DATETIME fields if multiple daily visits are allowed..

 

 

Once the data issues are resolved then we can start with the query.

[ajoo]

Hi barand, thanks for the response and yes this data is different from the earlier. I think there is a small mistake in the data in TABLE A ( the one in the previous post) so here's the new table again - TABLE B

 

Day_ID -- Dues ---  Last_Visit ---  Points.

1  --------- 900 -------- 1/12  --------   6     

2  --------- 700 -------- 4/12  --------   7

3  --------- 400 -------- 7/12  --------   4

4  --------- 600 -------- 9/12  --------   6

5  --------- 600 -------- 10/12 -------   6

6  --------- 500 -------- 10/12 -------   5

7  --------- 600 -------- 14/12 -------   6 

8  --------- 500 -------- 14/12 -------   5

 

ok so now I think its correct. Yes multiple dates are allowed. However I think that the dates got goofed in the sense that they should have followed order ( Ascending  I mean.). Ok but your keen observation has led me to another few questions.

1. I would like to ask that in a system where a person;'s visits to the club are entered serially, is it possible for the table to store them in an un-ordered manner as in TABLE A.

2. Even if the system enters inputs these dates in order, is there any manner that the table may get jumbled on dates (again as in TABLE A).

3. How can the same output be achieved for both the tables. I think if you just do that for me for the earlier TABLE A, I'll try and do the simpler TABLE B one myself.

 

Thanks very much.

[Barand]

With one person entering data serially it is still possible to accidentally miss one then enter it later. With more than one person there is no guarantee at all of maintaining the sequence.

 

If you have two records for 14/12 there is now way of knowing which was the later, therefore you need a time element

DROP TABLE IF EXISTS `clubvisit`;

CREATE TABLE `clubvisit` (
  `day_id` int(11) NOT NULL AUTO_INCREMENT,
  `dues` int(11) DEFAULT NULL,
  `last_visit` datetime DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  PRIMARY KEY (`day_id`)
) ;

INSERT INTO `clubvisit` VALUES 
(1,900,'2012-12-01 20:00:00',6),
(2,700,'2012-12-04 17:00:00',7),
(3,600,'2012-12-07 10:00:00',4),
(4,600,'2012-12-09 21:00:00',6),
(5,600,'2012-12-10 15:00:00',6),
(6,600,'2012-12-14 17:00:00',6),
(7,500,'2012-12-10 20:00:00',5),
(8,500,'2012-12-14 19:30:00',5);

Sorted into time order we now have

+--------+------+---------------------+--------+
| day_id | dues | last_visit          | points |
+--------+------+---------------------+--------+
|      1 |  900 | 2012-12-01 20:00:00 |      6 |
|      2 |  700 | 2012-12-04 17:00:00 |      7 |
|      3 |  600 | 2012-12-07 10:00:00 |      4 |
|      4 |  600 | 2012-12-09 21:00:00 |      6 |
|      5 |  600 | 2012-12-10 15:00:00 |      6 |
|      7 |  500 | 2012-12-10 20:00:00 |      5 |
|      6 |  600 | 2012-12-14 17:00:00 |      6 |
|      8 |  500 | 2012-12-14 19:30:00 |      5 |
+--------+------+---------------------+--------+

So given this data, what was the question again, and what would be the expected result?

[Barand]

With my data above, this might be what you want

SELECT day_id, dues, last_visit, points
FROM clubvisit
JOIN
    (
    SELECT cv.dues 
    FROM clubvisit cv
    JOIN
        (
        SELECT dues as lastdues
        FROM clubvisit
        JOIN
            (
            SELECT MAX(last_visit) as last_visit
            FROM clubvisit
            ) as latest 
            USING (last_visit)
        ) as A
        ON cv.dues >= A.lastdues
    GROUP BY cv.dues
    LIMIT 2
    ) duesvalues
USING (dues)
ORDER BY last_visit;

Which gives

+--------+------+---------------------+--------+
| day_id | dues | last_visit          | points |
+--------+------+---------------------+--------+
|      3 |  600 | 2012-12-07 10:00:00 |      4 |
|      4 |  600 | 2012-12-09 21:00:00 |      6 |
|      5 |  600 | 2012-12-10 15:00:00 |      6 |
|      7 |  500 | 2012-12-10 20:00:00 |      5 |
|      6 |  600 | 2012-12-14 17:00:00 |      6 |
|      8 |  500 | 2012-12-14 19:30:00 |      5 |
+--------+------+---------------------+--------+

Edited October 19, 2013 by Barand

[ajoo]

Hi barand, Thanks for this but i forgot to change the dues values when i changed the table order earlier. So it kind of changed the entire sense of what was needed. So I am just going to put the table back here corrected and which is as follows:

 

Day_ID -- Dues ---  Last_Visit ---  Points.

1  --------- 900 -------- 1/12  --------   9     

2  --------- 600 -------- 4/12  --------   6

3  --------- 400 -------- 7/12  --------   4

4  --------- 500 -------- 9/12  --------   5

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 11/12 -------   6

7  --------- 600 -------- 13/12 -------   6 

8  --------- 500 -------- 15/12 -------   5

9  --------- 500 -------- 19/12 -------   5

Ok so here's the modified table and I have changed the dates to be unique ( I understood that here we should have a date and time format for multiple entries on the same day) just to keep it simple. So now I am looking for the latest value of dues (500) . Then we move back on dates and the next is also 500, so we want that and then we come to dues value of 600 on Day_ID 7. Here the dues have changed. But now I want to know for how many earlier days were the dues 600. So we move back and find that till Day_ID = 5 or till on 10/12 the dues were same and equal to the value 600. That's it. Those are all the values I want. So my output should be :

 

5  --------- 600 -------- 10/12 -------   6

6  --------- 600 -------- 11/12 -------   6

7  --------- 600 -------- 13/12 -------   6 

8  --------- 500 -------- 15/12 -------   5

9  --------- 500 -------- 19/12 -------   5

 

I have checked and rechecked the table. Its correct. Please guide how this may be achieve achieved.

Thanks loads !

[Barand]

Some things are easier in the script

include ("/db_inc.php");
$db=new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$sql = "SELECT day_id, dues, last_visit, points 
    FROM clubvisit 
    ORDER BY last_visit DESC";
$res = $db->query($sql);

$val = $nextval = 0;
$results = array();

$row = $res->fetch_assoc();  // get first row
$val = $row['dues'];
do {
    if ($row['dues'] != $val) {
        if ($nextval==0) {
            $val = $row['dues'];
            $nextval = 1;
        }
        else break;
    }
    $results[] = $row;
} while ($row = $res->fetch_assoc());
$results = array_reverse($results);

echo '<pre>';
foreach ($results as $rec) {
    echo join("\t", $rec)."\n";
}
echo '</pre>';

RESULTS

3	600	2012-12-07 10:00:00	4
4	600	2012-12-09 21:00:00	6
5	600	2012-12-10 15:00:00	6
7	600	2012-12-10 20:00:00	6
6	500	2012-12-14 17:00:00	5
8	500	2012-12-14 19:30:00	5

[ajoo]

Hi Barand, Thanks for this. I have to check it out yet cos you've used the earlier table. I have studied the code and got the drift though. Still I'll try it out and revert.  

 

Meanwhile I have another twist on the previous Query that you solved below:

SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >
    (
    SELECT MAX(last_visit) FROM clubvisit                                            --+
    WHERE points <>                                                                    | 
        (                                                                              | find the
        SELECT points as lastpoints                             --+                    | latest date
        FROM clubvisit                                            | find points        | that had a
        JOIN                                                      | value from         | point value
            (                                                     | the record that    | not equal to
            SELECT MAX(last_visit) as last_visit    --+ get       | matches the        | points value
            FROM clubvisit                            | latest    | latest date in     | found in the
            )                                       --+ date      | the subquery by    | latest record
            as latest USING (last_visit)                        --+ JOIN on the date --+
        )
    )

This works great. However what if the MAX(last_visit) had to check for and ensure that the dues on this day was not NULL. If the dues was NULL in the last row ( highest date)then this row had not to be considered in the rest of the query. 

 

So i tried to get the latest date as follows:-

JOIN
 (
  SELECT MAX(last_visit) as last_visit
  FROM (SELECT* FROM clubvisit as cv WHERE dues IS NOT NULL)
 ) as latest USING (last_visit);

But this gave an error " #1248 - Every derived table must have its own alias" .  even though "SELECT* FROM clubvisit as cv WHERE dues IS NOT NULL" gave me the subset i wanted. So i don;t know how to eliminate this error though I tried.  I even tried to use this statement as the first statement in the query and then work with the alias but that gave an error too. So how do i get there now ?

 

Just for clarity if the Table is as below :

    (1  , 900 , '2012-12-01'  ,   6),
    (2  , 700 , '2012-12-04'  ,   7),
    (3  , 600 , '2012-12-07'  ,   5),
    (4  , 600 , '2012-12-09'  ,   6),
    (5  , 600 , '2012-12-10'  ,   6),
    (6  , 600 , '2012-12-14'  ,   6),
    (7  , NULL, '2012-12-14'  ,   NULL);

 

Then the last_value has to be that of date 14-12-2012 or day_id = 7 and the desired table should be calculated with corresponding point value of 6.

 

The output should be as before :-

+--------+------+------------+--------+
| day_id | dues | last_visit | points |
+--------+------+------------+--------+
|      4 |  600 | 2012-12-09 |      6 |
|      5 |  600 | 2012-12-10 |      6 |
|      6 |  600 | 2012-12-14 |      6 |
+--------+------+------------+--------+

Thanks !

[Barand]

The inner subquery needs to be

SELECT MAX(last_visit) as last_visit
FROM clubvisit
WHERE dues IS NOT NULL

[ajoo]

Thanks Barand, that worked great. It was also getting the null record which i managed to eliminate by adding to the end of the query "AND dues IS NOT NULL";

 

Now i'll check out the earlier reply from you ( some things work better in code) and get back with the results. You have been awesome help and guide. 

 

Thanks loads. 

[ajoo]

Hi All & Guru Barand,

 

This issue had been completely resolved, thanks to a lot of help from Guru Barand. I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.point1, cv.point2, cv.point3 etc. So now I need to make a selection based on each of these cv.points columns.

 

As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in juts one go.

 

The values that I require for each of the cv.point columns is day_id of the previous / old day on which the cv.points value changed from the current day value, and ofcourse that value  of cv.points on that old/ previous day.

 

So for  example if the table is as below:

 

Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2.

1  --------- 900 -------- 1/12  -----------   9  ------------ 5   

2  --------- 600 -------- 4/12  -----------   6  ------------ 6

3  --------- 400 -------- 7/12  -----------   4  ------------ 7

4  --------- 500 -------- 9/12  -----------   5  ------------ 8

5  --------- 600 -------- 10/12 ----------   6  ------------ 8

6  --------- 600 -------- 11/12 ----------   6  ------------ 8

7  --------- 600 -------- 13/12 ----------   6  ------------ 7

8  --------- 500 -------- 15/12 ----------   5  ------------ 7

9  --------- 500 -------- 19/12 ----------   5  ------------ 7

 

Then I need the following set of values :

1. day_id1 -- Day 7,  points1 -- 6  days_diff1 -- (9-7 = 2)

2. day_id2 -- Day 6,  points2 -- 8  days_diff  -- (9-6 = 3)

 and so on for other points.

 

As of now I calculate each of these values separately by running the query for each of the cv,points but I am sure that's not the best way to do it. 

Kindly help refine this. 

 

Thanks all !

  

[ajoo]

I think I did not get any reply becos of the solved status on this query. So removing that status(sorry Guru Barand). Here's the same issue but with a lilttle twist and complexity I suppose. SO here's my last message again.

 

Hi All & Guru Barand,

 

This issue had been completely resolved, thanks to a lot of help from Guru Barand. I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.point1, cv.point2, cv.point3 etc. So now I need to make a selection based on each of these cv.points columns.

 

As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in juts one go.

 

The values that I require for each of the cv.point columns is day_id of the previous / old day on which the cv.points value changed from the current day value, and ofcourse that value  of cv.points on that old/ previous day.

 

So for  example if the table is as below:

 

Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2.

1  --------- 900 -------- 1/12  -----------   9  ------------ 5   

2  --------- 600 -------- 4/12  -----------   6  ------------ 6

3  --------- 400 -------- 7/12  -----------   4  ------------ 7

4  --------- 500 -------- 9/12  -----------   5  ------------ 8

5  --------- 600 -------- 10/12 ----------   6  ------------ 8

6  --------- 600 -------- 11/12 ----------   6  ------------ 8

7  --------- 600 -------- 13/12 ----------   6  ------------ 7

8  --------- 500 -------- 15/12 ----------   5  ------------ 7

9  --------- 500 -------- 19/12 ----------   5  ------------ 7

 

Then I need the following set of values :

1. day_id1 -- Day 7,  points1 -- 6  days_diff1 -- (9-7 = 2)

2. day_id2 -- Day 6,  points2 -- 8  days_diff  -- (9-6 = 3)

 and so on for other points.

 

As of now I calculate each of these values separately by running the query for each of the cv,points but I am sure that's not the best way to do it. 

Kindly help refine this. 

 

Thanks all !