SELECT URL,SearchTerms,
CASE WHEN Seq=1 THEN ProductID ELSE NULL END AS ProductID,
CASE WHEN Seq=1 THEN NumOFHits ELSE NULL END AS NumOFHits
FROM
(
SELECT URL,SearchTerms,ProductID,COUNT(*) OVER(PARTITION BY ProductID) AS NumOfHits,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SearchTerms) AS Seq
FROM Table)t
ORDER BY NumOFHits DESC,ProductID ASC

quote:

---

Originally posted by visakh16

make be this

SELECT URL,SearchTerms,
CASE WHEN Seq=1 THEN ProductID ELSE NULL END AS ProductID,
CASE WHEN Seq=1 THEN NumOFHits ELSE NULL END AS NumOFHits
FROM vWTable
(
SELECT URL,SearchTerms,ProductID,COUNT(*) OVER(PARTITION BY ProductID) AS NumOfHits,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SearchTerms) AS Seq
FROM vWTable)t
ORDER BY NumOFHits DESC,ProductID ASC

---

quote:

---

Originally posted by Vaishu

HI

I am getting Incorrect syntax near second select command. Also NumOfHits is the counts of ProdutId.

quote:

---

Originally posted by visakh16

make be this

SELECT URL,SearchTerms,
CASE WHEN Seq=1 THEN ProductID ELSE NULL END AS ProductID,
CASE WHEN Seq=1 THEN NumOFHits ELSE NULL END AS NumOFHits
FROM vWTable
(
SELECT URL,SearchTerms,ProductID,COUNT(*) OVER(PARTITION BY ProductID) AS NumOfHits,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SearchTerms) AS Seq
FROM vWTable)t
ORDER BY NumOFHits DESC,ProductID ASC

---

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Vaishu

HI

I am getting Incorrect syntax near second select command. Also NumOfHits is the counts of ProdutId.

quote:

---

Originally posted by visakh16

make be this

SELECT URL,SearchTerms,
CASE WHEN Seq=1 THEN ProductID ELSE NULL END AS ProductID,
CASE WHEN Seq=1 THEN NumOFHits ELSE NULL END AS NumOFHits
FROM vWTable
(
SELECT URL,SearchTerms,ProductID,COUNT(*) OVER(PARTITION BY ProductID) AS NumOfHits,
ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SearchTerms) AS Seq
FROM vWTable)t
ORDER BY NumOFHits DESC,ProductID ASC

---

---

this is not what i posted. remove the view name near FROM

---