Geek Answers Handbook

Using a separate function in SQL

I have an SQL query that I am running. What I wanted to know is that there is a way to select rows in a table, where the value inside one of these columns is different? When I use a separate function, it returns all the individual rows, so ...

select distinct teacher from class etc.

This works fine, but I select multiple columns, so ...

select distinct teacher, student etc.

but I don’t want to extract individual lines, I need different lines in which the teacher is different. So this query is likely to return the same teacher name several times because the value for the student is different, but I would like to return strings in which the teachers are different from each other, even if it means returning the teacher and one student name (because I do not need all students).

I hope that what I'm trying to ask is clear, but is it possible to use a separate function in the same column even when selecting multiple columns, or is there any other solution to this problem? Thank.

, . , "" . .., , . ( 10) . , , . , . , , , , , . . , , , .

Col ​​A Col B Col C Col D

a b c d

a c d b

b a a c

b c c c

, , . , Col A . ?

+3
18

DISTINCT. , . SELECT a, DISTINCT(b), c, DISTINCT(d) FROM SomeTable. DISTINCT , .. , SELECT DISTINCT.

: DISTINCT .

, , GROUP BY. , , , , , , COUNT(). :

SELECT teacher, COUNT(student) AS amountStudents
FROM ...
GROUP BY teacher;
+11

- GROUP BY Col A. :

SELECT * FROM table_name GROUP BY Col A

:

ABCD

BAAC

+2

, ,

SELECT * FROM class WHERE teacher IN (SELECT DISTINCT teacher FROM class)

,

+2

, ( , / , ..), .

  • , "" " ", WHERE. - DISTINCT GROUP BY , , . , MySQL , , GROUP BY, MSSQL , GROUP BY. "", .

  • , .

:

SELECT t1.a
        , (SELECT TOP 1 b FROM Table1 t2 WHERE t1.a = t2.a) AS b
        , (SELECT TOP 1 c FROM Table1 t2 WHERE t1.a = t2.a) AS c
        , (SELECT TOP 1 d FROM Table1 t2 WHERE t1.a = t2.a) AS d
    FROM dbo.Table1 t1
    WHERE (your criteria here)
    GROUP BY t1.a

, , . ORDER BY , .

+2

distinct . , , , .

distinct , .

+1

soulmerge Shiraz , GROUP BY . .

DECLARE @table TABLE (
    [Teacher] [NVarchar](256) NOT NULL ,
    [Student] [NVarchar](256) NOT NULL 
)

INSERT INTO @table VALUES ('Teacher 1', 'Student 1')
INSERT INTO @table VALUES ('Teacher 1', 'Student 2')
INSERT INTO @table VALUES ('Teacher 2', 'Student 3')
INSERT INTO @table VALUES ('Teacher 2', 'Student 4')

SELECT 
    T.[Teacher],  
    (
        SELECT TOP 1 T2.[Student]
        FROM @table AS T2 
        WHERE T2.[Teacher] = T.[Teacher]
    ) AS [Student]
FROM @table AS T
GROUP BY T.[Teacher]


Teacher 1, Student 1
Teacher 2, Student 3
+1

, -1 , .

0

"GROUP BY teacher", , .

0

, ?

, ?

select class_name, count(teacher) 
from class group by class_name having count(teacher)=1

?

select teacher, count(student) 
from class group by teacher having count(student)=1

- ? , , , DISTINCT , . , . , DISTINCT?

0

, , , .

, .

select distinct teacher from class
0

, ... - :

SELECT DISTINCT ColA FROM Table WHERE ...

, .

, .

0

GROUP BY .

0

, , , , Distinct

Select Distinct column1 -- where your criteria...
0
select cola,colb,colc 
from yourtable
where cola in 
(
  select cola from yourtable where your criteria group by cola having count(*) = 1
)
0

. , .

, .

   Select teacher_id, count(*)
    from teacher_table inner join classes_table
    on teacher_table.teacher_id = classes_table.teacher_id
    group by teacher_id
0

, , , . .

Select * from tbl
Where ColA in (Select ColA from tbl Group by ColA Having Count(ColA) = 1)

This will return all data from rows where ColA is unique - i.e. there is no other row with the same ColA value. Of course, this means zero rows from the provided sample data.

0
source 
declare @temp as table (colA nchar, colB nchar, colC nchar, colD nchar, rownum int)

insert @temp (colA, colB, colC, colD, rownum)
select Test.ColA, Test.ColB, Test.ColC, Test.ColD, ROW_NUMBER() over (order by ColA) as rownum
    from Test

select t1.ColA, ColB, ColC, ColD
from @temp as t1
join (
    select ColA, MIN(rownum) [min]
    from @temp
    group by Cola)
 as t2 on t1.Cola = t2.Cola and t1.rownum = t2.[min]

This will return one row for each colA value.

0
source 
CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
0
source

More articles:


All Articles