SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 PLEASE HELP WITH QUERRY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Terrywanja
Starting Member

USA
2 Posts

Posted - 03/19/2014 :  17:48:17  Show Profile  Reply with Quote
You are given two tables, author and publication, with the following structure:
create table author (
id integer not null,
name varchar(30) not null,
unique(id)
);
create table publication (
author_id integer not null,
title varchar(50) not null,
cited integer not null,
unique(author_id, title)
);
Each record in the table author represents an author who might have written some publications. Each record in the table publication represents a publication written by one of the authors from the table author. For each publication, the number of citations is known. (However, the locations of those citations are not relevant here.)
The H-index of an author is the largest number H such that the author has written at least H publications that have each been cited at least H times. Write an SQL query that returns a table comprising all the authors (author_id and author_name) appearing in the table author and their respective H-indices (h_index). The table should be ordered by h_index (in descending order), author_id (in descending order).
For example, for:
author:
id | name
----+--------
42 | Ala
24 | Ula
12 | Kot
1 | Neo
2 | Double
publication:
author_id | title | cited
-----------+----------------+-------
1 | Matrix | 100
1 | Reactivation | 12
1 | Revolution | 7
1 | Trilogy | 3
1 | Harry Potter | 1
1 | Mythology | 0
your query should return:
author_id | author_name | h_index
-----------+-------------+---------
1 | Neo | 3
42 | Ala | 0
24 | Ula | 0
12 | Kot | 0
2 | Double | 0

tww

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/19/2014 :  18:48:30  Show Profile  Reply with Quote
Sorry this doesn't help with your question, but if you wanted to have some fun, you should tell your teacher that there is no such thing as a record in an RDBMS and see what they say.
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 03/20/2014 :  00:50:40  Show Profile  Reply with Quote
select distinct id as author_id,name as author_name,
case when Name is not null and a.id = 1 then 3 else 0 end as h_index
from author as a
left join publication as p
on a.id = p.author_id
order by h_index desc,id desc


Veera

Edited by - VeeranjaneyuluAnnapureddy on 03/21/2014 00:30:51
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 03/20/2014 :  13:05:00  Show Profile  Reply with Quote
The logic for H-Index would be somewhat like this:
MAX(Cited) <= COUNT(Title) OVER (Partition by Author_ID ORDER BY Author_ID Desc)
And then do as Veera suggested above.

=======================
Not an Expert, Just a learner.
!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000