[FIXED] How do I (or can I) SELECT DISTINCT on multiple columns?

Issue

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.

So I'm thinking:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

But my brain hurts going any farther than that.

Solution

SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

SELECT a,b,c FROM t GROUP BY a,b,c

It's a good idea to get used to the GROUP BY syntax, as it's more powerful.

For your query, I'd do it like this:

UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
    SELECT id
    FROM sales S
    INNER JOIN
    (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1 
    ) T
    ON S.saleprice=T.saleprice AND s.saledate=T.saledate
 )

Answered By - Joel Coehoorn

Answer Checked By - David Marino (PHPFixing Volunteer)

Read More

[FIXED] Why doesn't LINQ include a `distinct` keyword?

Issue

NOTE: Before you read on or provide an answer, I know about Enumerable.Distinct, I am asking about specific language support for that method, not about the method itself.

I've always wondered why there is no distinct keyword in the C# LINQ keyword set so that I could write:

var items = distinct from x in y
            select x;

or

var items = from x in y
            select distinct x;

Anybody know why this wasn't included or why it would be a bad idea to include it? It just feels cumbersome to me that I have to wrap the query just to call Distinct(); a distinct keyword would feel more natural.

NOTE: I know that the Distinct method has overrides to provide a comparer if that is required, but a keyword that uses the default comparer would be great. I could even imagine a distinct by keyword combination so that a comparison operator could be provided inline to the query.

Solution

Charlie Calvert has a blog post ("Using Distinct and Avoiding Lambdas") discussing the issue. From the top of the post:

1. Most query operators such as Select(), Where() and GroupBy() take something called a lambda as a parameter. 2. Lambdas are difficult to write. 3. Query expressions were created in large part to allow developers to use LINQ without having to learn the complex syntax associated with lambdas. 4. A few query operators, such as Distinct(), do not take lambdas as parameters. As a result, they are easy to call. 5. Query expressions were therefore not created for operators such as Distinct() that do not take lambdas.

And also, from further down in the post:

Query operators are method calls. In other words, there are methods in the LINQ API called Select(), Group(), Distinct(), etc. We don't usually call these methods directly because they take lambdas as parameters, and many people find that lambdas are hard to understand. To help developers avoid the complex task of writing lambdas, the team invented query expressions, which are a "syntactic sugar" that sit on top of lambdas.

TL;DR: There's no distinct keyword for simplicity's sake, since distinct does not take a lambda expression.

Answered By - Donut

Answer Checked By - David Goodson (PHPFixing Volunteer)

Read More

[FIXED] Is it possible to have a DISTICT statement only on one varible?

Issue

I only want the SELECT DISTINCT statement on 'yearLevel' (as I don't want any duplicates only on yearLevel)

image of data displayed on screen

For example I only want one 'yearLevel' 12 record in the table, and for that to be the one with the lowest 'totalSeconds'

code

$sql = "SELECT firstName, lastName, yearLevel, totalSeconds 

FROM studentInformation
  JOIN record
    ON studentInformation.studentId = record.student_Id

    ORDER BY totalSeconds ASC 
    LIMIT  1  "; 

Is it possible to do this -

$sql = "SELECT firstName, lastName, DISTINCT yearLevel, totalSeconds 

    FROM studentInformation
      JOIN record
        ON studentInformation.studentId = record.student_Id

        ORDER BY totalSeconds ASC 
        LIMIT  1  "; 

Solution

Provided that "firstName" and "lastName" are in table "studentInformation", and "yearLevel" as well as "totalSeconds" are in table "record" this query should be working. It uses a correlated subquery. I did not test this; if it doesn't work please let me know.

    SELECT a.firstName, a.lastName, b.yearLevel, b.totalSeconds
      FROM studentInformation a
INNER JOIN record b ON a.studentId = b.studentId
     WHERE b.totalSeconds = ( SELECT min(totalSeconds)
                                FROM record
                               WHERE yearLevel = b.yearLevel )
  ORDER BY b.totalSeconds ASC

Assuming that only "totalSeconds" is in table "record" this could work, too.

    SELECT a.firstName, a.lastName, a.yearLevel, b.totalSeconds
      FROM studentInformation a
INNER JOIN record b ON a.studentId = b.studentId
     WHERE b.totalSeconds = ( SELECT MIN(d.totalSeconds)
                                FROM studentInformation c
                          INNER JOIN record d ON c.studentId = d.studentId
                               WHERE c.yearLevel = a.yearLevel )
  ORDER BY b.totalSeconds ASC

Answered By - rf1234

Read More

[FIXED] Distinct values with pluck

Issue

I'm trying to retrieve data from database and bind them to a html select tag, and to bind them i need to use pluck so i get the field i want to show in a array(key => value), because of FORM::select. The normal pluck gets all the results, while i want to use distinct. My model is Room and it looks like:

    class Room extends Eloquent
{
    public $timestamps = false;

    protected $casts = [
        'price' => 'float',
        'floor' => 'int',
        'size' => 'float'
    ];

    protected $fillable = [
        'capacity',
        'description',
        'price',
        'floor',
        'size',
        'type',
        'photo_name'
    ];
}

While my function I'm using in the controller look like:

public function getRooms()
    {
        $roomType = Room::pluck('type','type');
        $roomFloor = Room::pluck('floor','floor');

        return view('roomgrid')->with('type',$roomType)->with('floor',$roomFloor);
    }

And my view contains this piece of code to get floors:

{{FORM::select('floor', $floor, null,['class'=>'basic'])}}

Like this i get duplicated floors, that i don't want. Is there any way so i can get distinct floors and pluck them? Thanks in advance.

Solution

Why not use groupBy()?

$roomType = Room::groupBy('type')->pluck('type','type');

Answered By - Buglinjo

Read More